How do I get the sum of a SQL rollup statement to the top of the resultset

Question

I have a SQL query

Select city, sum (tax) 
from db 
group by rollup (city)

which is returning:

City tax 
A 100
B 300
Null 400

So I get the "sum of sums" via rollup.

How can I get the "sum of sums" to the top of the resultset like

null 400
A 100
B 300

I want to use it in Excel later and don't know the number of lines beforehand.

I tried a subquery, but my database doesn't allow it and I tried order by

Answer 1

By default you should be specifying the expected sort order, if you do not then the rollup will be appended to the end.

So the first step is to specify that you want to sort by the city column in ascending order. But different RDBMS treat NULL differently, in your case as judged by the ROLLUP syntax, you will need to use the NULLS FIRST option to ensure the nulls are processed first in the sort:

SELECT city, sum (tax) as tax
FROM db
GROUP BY ROLLUP city ASC NULLS FIRST

city	tax
A	100
B	700
C	300
null	400

Db fiddle for ORACLE: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=1f2ba9694eba76986e4db902e94b9c6d
NOTE: The totals have been changed to illustrate different sort sequeces

In MS SQL Server nulls are treated as the lowest value in any sort, so we don't need any special trickery: Notice the different rollup syntax

SELECT City, sum (tax) as tax
FROM db
GROUP BY city WITH ROLLUP
ORDER BY city

city	tax
null	400
A	100
B	700
C	300

Things get complicated in MS SQL when you want to have the rollup record displayed first, but sort by an aggregate column. In this case we need to be a little bit more complicated with the sort order:

SELECT City, sum (tax) as tax
FROM db
GROUP BY city WITH ROLLUP
ORDER BY CASE WHEN city is NUll THEN 1 END DESC, tax desc;

city	tax
null	400
B	700
C	300
A	100

See this fiddle for MS SQL Server: http://sqlfiddle.com/?#!18/eb3aa/1