Using ROLL UP/CUBE in conjunction with PIVOT [Oracle]

Question

I have a table in Oracle that looks like this:

year    month   customer
------------------------
2011    Jan     Smith
2011    Jan     Smith
2012    Feb     Howard
2013    Feb     Howard
...

Now I want to make it like this:

year    Jan     Feb     ...     Dec     ytotal
-----------------------------------------------
2011    3       1       ...     5       27
2012    1       4       ...     11      45
...                                     ...
2018    9       1       ...     1       21
mtotal  35      19              51      275 

Where the numbers in each cell corresponds to DISTINCT count of names of customers.

When I attempt to do this query:

SELECT DECODE(GROUPING(year), 1, 'mtotal:', year) year,
       DECODE(GROUPING(month), 1, 'ytotal:', month) month,
       COUNT(DISTINCT customer) AS cust_count
FROM mytable
GROUP BY ROLLUP(year, month)

I get this intermediate result:

year    month   cust_count
--------------------------
2011    Jan     3
2011    Feb     1
...
2011    Dec     5
2011    ytotal  27
2012    Jan     1
2012    Feb     4
...
2012    Dec     11
2012    ytotal  45
...
2018    Jan     9
2018    Feb     1
...
2018    Dec     1
2018    ytotal  21
mtotal  ytotal  275

When I use this as a subquery then do a pivot:

SELECT * FROM (
    SELECT DECODE(GROUPING(year), 1, 'mtotal:', year) year,
           DECODE(GROUPING(month), 1, 'ytotal:', month) month,
           COUNT(DISTINCT customer) AS cust_count
    FROM mytable
    GROUP BY ROLLUP(year, month)
) 
PIVOT (
    COUNT(month) FOR month IN ('Jan', 'Feb', ..., 'Dec', 'ytotal')
)

I do not get the expected result. Please include the use of ROLL UP/CUBE and PIVOT in the answer.

Answer 1

With grouping by rollup(year, month) you had no sums for same month from different years. So I used cube and slightly modified your query, please check it:

select * 
  from (select case when grouping(year)  = 1 then 'ysum' else to_char(year) end year,
               case when grouping(month) = 1 then 'msum' else to_char(month) end month,
               count(distinct customer) as cnt
          from mytable
          group by cube(year, month) )
  pivot (sum(cnt) for month in ('Jan', 'Feb', 'Dec', 'msum'))
  order by year

_demo

---

Edit:

If in summing columns you need sums of distinct counts then make basic grouping at first, then use cube. And pivot at the end. Aggregating function in pivot is not important, because you have already counted values, one per each row / column.

select * 
  from (
    select nvl(to_char(year), 'ys') year, nvl(to_char(month), 'ms') month, sum(cnt) cnt
      from (
        select year, month, count(distinct customer) cnt 
          from mytable 
          group by year, month)
      group by cube(year, month))
  pivot (sum(cnt) for month in ('Jan', 'Feb', 'Dec', 'ms')) 
  order by year