I have the table below with the data. Here the code:
create table t(mon number, id number)
insert into t(mon, id)
SELECT 4, 1006 FROM DUAL UNION ALL
SELECT 5, 10618 FROM DUAL UNION ALL
SELECT 2, 9999 FROM DUAL UNION ALL
SELECT 2, 9999 FROM DUAL UNION ALL
SELECT 2, 1000 FROM DUAL UNION ALL
I wish to add sums in the last row.
I tried with this query:
select id,
coalesce(case when max(mon) = 1 then count(*) end, 0) Jan,
coalesce(case when max(mon) = 2 then count(*) end, 0) Feb,
coalesce(case when max(mon) = 3 then count(*) end, 0) Mar,
coalesce(case when max(mon) = 4 then count(*) end, 0) Apr,
coalesce(case when max(mon) = 5 then count(*) end, 0) May,
coalesce(case when max(mon) = 6 then count(*) end, 0) Jun,
count(*) from t group by rollup(id);
But I’m getting:
0 0 0 0 36 0 36
I wish to get:
0 2 0 1 33 0 36
The whole table with data is in DB Fiddle