By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t1 (
ClientID NUMBER,
Service varchar(25) NOT NULL,
Status varchar(25) NOT NULL,
CloseDate date
);
INSERT ALL
INTO t1(ClientID,Service,Status,CloseDate) VALUES (1,'A','Closed','31-may-20')
INTO t1(ClientID,Service,Status,CloseDate) VALUES (1,'A','Open',null)
INTO t1(ClientID,Service,Status,CloseDate) VALUES (1,'A','Open',null)
INTO t1(ClientID,Service,Status,CloseDate) VALUES (2,'A','Open',null)
INTO t1(ClientID,Service,Status,CloseDate) VALUES (3,'A','Closed','31-may-20')
select * from dual
5 rows affected
SELECT ClientID,
Service,
Status,
CloseDate,
coalesce((SELECT COUNT(*) OVER(PARTITION BY clientid ORDER BY clientid) FROM t1 WHERE t1.ClientID = t.ClientID and t1.CloseDate IS NULL FETCH FIRST 1 ROWS ONLY),0) AS OpenCount
FROM t1 t;
| CLIENTID | SERVICE | STATUS | CLOSEDATE | OPENCOUNT |
|---|---|---|---|---|
| 1 | A | Closed | 31-MAY-20 | 2 |
| 1 | A | Open | null | 2 |
| 1 | A | Open | null | 2 |
| 2 | A | Open | null | 1 |
| 3 | A | Closed | 31-MAY-20 | 0 |