I have a SQL query

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

Giving

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 subquerry but my db doesn't allow it and I tried order by

CodePudding user response：

you can use an order by with the nulls first option:

Select city, sum (tax) 
from db group by rollup (city) 
order by city asc nulls first

CodePudding user response：

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

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

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;

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