CODEWITHSUNDEEP
sqlsql-server
nicomp
nicomp

Reputation: 4647

How do I move the Grand Total to the bottom of the results set using ROLLUP?

This is my table: enter image description here

This is my query and the results set:

SELECT S.STATE AS State,
       S.City AS City , COUNT(City) AS [Stores in City]
FROM tStore S
GROUP BY ROLLUP(STATE, City)
ORDER BY State, COUNT(City)

enter image description here

The grand total row is at the top! How do I make it appear at the bottom?

Upvotes: 0

Views: 1614

Answers (4)

Zack
Zack

Reputation: 2341

You can use a combination of the actual field and the GROUPING clause in your ORDER BY:

ORDER BY 
    GROUPING(State),
    State,
    GROUPING(City),
    City

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can also use grouping sets:

GROUP BY GROUPING SETS ( (STATE, City), (STATE), () )

Upvotes: 0

Piotr Palka
Piotr Palka

Reputation: 3169

Using state State IS NULL will not correctly sort rows with NULL value.

You should use ORDER BY GROUPING(State), ... to force total to last row.

Upvotes: 1

Chris Hackett
Chris Hackett

Reputation: 449

Your "ORDER BY State" is causing the NULL value to rise to the top. Just change your "ORDER BY" to something like:

ORDER BY case when State IS NULL then 2 else 1 end

Upvotes: 1

Related Questions