Using ROLLUP to add sums in the last row

Question

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

Answer 1

Aggregate by id first and then do the ROLLUP to get the totals (if you try to do it all in one then the ROLLUP row will use the global maximum and not the maximum for each id).

SELECT id,
       SUM(CASE max_mon WHEN 1 THEN total ELSE 0 END) AS Jan,
       SUM(CASE max_mon WHEN 2 THEN total ELSE 0 END) AS Feb,
       SUM(CASE max_mon WHEN 3 THEN total ELSE 0 END) AS Mar,
       SUM(CASE max_mon WHEN 4 THEN total ELSE 0 END) AS Apr,
       SUM(CASE max_mon WHEN 5 THEN total ELSE 0 END) AS May,
       SUM(CASE max_mon WHEN 6 THEN total ELSE 0 END) AS Jun,
       SUM(total) AS total
FROM   (
  SELECT id,
         MAX(mon) AS max_mon,
         COUNT(*) AS total
  FROM   t
  GROUP BY id
)
GROUP BY ROLLUP(id);

Which, for the sample data, outputs:

ID	JAN	FEB	MAR	APR	MAY	JUN	TOTAL
1006	0	0	0	1	0	0	1
10618	0	0	0	0	1	0	1
9999	0	2	0	0	0	0	2
1000	0	0	0	0	9	0	9
13071	0	0	0	0	8	0	8
1009	0	0	0	0	11	0	11
15344	0	0	0	0	1	0	1
14337	0	0	0	0	1	0	1
990	0	0	0	0	1	0	1
10907	0	0	0	0	1	0	1
null	0	2	0	1	33	0	36

fiddle