Order after rollup - oracle 11g

Question

I am using rollup() function in group by expression to show the result query, but my output like this:

Year    Month        AmountOfMoney
====    =====        =============
1998    March        130.00
1998    April        450.00    
1988    -            580.00
1999    January      120.00
1999    April        550.00
1999    -            670.00
-       -            1250.00 

how to make it order by total, then years, then (year,month)

Year    Month        AmountOfMoney
====    =====        =============
-       -            1250.00 
1988    -            580.00
1999    -            670.00
1998    March        130.00
1998    April        450.00    
1999    January      120.00
1999    April        550.00

Answer 1

I'm not a ORACLE user but I've found an interesting article about it:

Oracle SQL GROUP BY – The Complete Guide

Scrolling down till the GROUPING Function you'll find and example on how use GROUP BY with ROLLUP:

_{Quoted from the article}

There are several SQL functions related to grouping that can help you identify and work with groups.

Earlier in our examples, we noticed that subtotals had values of NULL for the columns that weren’t being considered in the group.

How can we tell which values are subtotals and which values are NULL because of the underlying data?

This is what the GROUPING function can help with.

The GROUPING function takes a single column as a parameter. The output is either 1 or 0:

It returns 1 if the column is being used as part of a subtotal and is showing NULL. It returns 0 if the underlying value is NULL or any other value.

You can also order by these columns, to have the subtotals at the start or the end, instead of in the middle of the results.

According to this you could use the next syntax to produce the expected result:

SELECT
    Year,
    Month,
    SUM(Amount) as AmoutOfMone
FROM
    YourTable
GROUP BY ROLLUP (Year, Month)
ORDER BY
    GROUPING(Year) DESC,
    GROUPING(Month) DESC,
    Year ASC,
    Month ASC;

Due this question has a previous question here, I've used my fiddle to add this new information and it works properly.

/* aggregated sales by year-month */
SELECT
    T.CALENDAR_YEAR AS Year,
    T.CALENDAR_MONTH_DESC AS Month,
    SUM(S.AMOUNT_SOLD) AS AmountOfMoney
FROM
    SALES S
JOIN
    TIMES T
    ON T.TIME_ID = S.TIME_ID
WHERE S.PROD_ID = 1
GROUP BY ROLLUP (T.CALENDAR_YEAR, T.CALENDAR_MONTH_DESC)
ORDER BY GROUPING (T.CALENDAR_YEAR) DESC, 
         GROUPING (T.CALENDAR_MONTH_DESC) DESC, 
         CALENDAR_YEAR ASC,
         CALENDAR_MONTH_DESC ASC;

YEAR | MONTH | AMOUNTOFMONEY
---: | ----: | ------------:
null |  null |           450
2018 |  null |           450
2018 |     1 |           100
2018 |     2 |           200
2018 |     3 |           150

db<>fiddle here