By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table testd
(
Source_id varchar2(20),
job_status varchar2(20)
);
insert into testd testd values('ABC', 'canceled');
1 rows affected
insert into testd testd values('ABC', 'failed');
1 rows affected
insert into testd testd values('ABC', 'finished');
1 rows affected
insert into testd testd values('DEF', 'canceled');
1 rows affected
insert into testd testd values('DEF', 'failed');
1 rows affected
insert into testd testd values('DEF', 'finished');
1 rows affected
insert into testd testd values('DEF', 'canceled');
1 rows affected
insert into testd testd values('DEF', 'failed');
1 rows affected
insert into testd testd values('DEF', 'finished');
1 rows affected
insert into testd testd values('DEF', 'canceled');
1 rows affected
insert into testd testd values('PQR', 'failed');
1 rows affected
insert into testd testd values('PQR', 'finished');
1 rows affected
select DISTINCT Source_id, job_status,
count(job_status) over (partition by Source_id, job_status) as r
from testd;
| SOURCE_ID | JOB_STATUS | R |
|---|---|---|
| ABC | canceled | 1 |
| PQR | finished | 1 |
| PQR | failed | 1 |
| DEF | canceled | 3 |
| DEF | failed | 2 |
| ABC | finished | 1 |
| ABC | failed | 1 |
| DEF | finished | 2 |
SELECT Source_id, job_status, COUNT(*)
FROM testd
GROUP BY Source_Id, job_status;
| SOURCE_ID | JOB_STATUS | COUNT(*) |
|---|---|---|
| PQR | finished | 1 |
| DEF | canceled | 3 |
| PQR | failed | 1 |
| ABC | canceled | 1 |
| ABC | failed | 1 |
| DEF | finished | 2 |
| ABC | finished | 1 |
| DEF | failed | 2 |