CODEWITHSUNDEEP
sqlsql-servergroup-bysql-order-byrollup
user5441558
user5441558

Reputation:

ORDER BY without Total in SQL

I have this SQL query, which uses ROLLUP to get the total but also needs to order every other row by the amount:

SELECT 
    ISNULL([SharepointId], 'Total') as 'Sharepoint', 
    Count([Id]) as 'Nombre de tickets' 
FROM [dbo].[Tickets]
GROUP BY ROLLUP([SharepointId])
ORDER BY 2 DESC, 1

Is there a way to make sure the total stays at the end of the results while keeping the descending order of the other rows?

For now, this is what I get:

Sharepoint         Nombre de tickets
------------------ -----------------
Total              20326
INTERNE            9396
MAIN               1503
...

Upvotes: 0

Views: 406

Answers (4)

MatBailie
MatBailie

Reputation: 86765

Other answers are mis-representing what you want.

  • You don't want a row to be last because it's NULL
  • You want a row to be last because it's been grouped


SQL Server has a function for exactly that, and the docs page has almost exactly your example in it... 


SELECT 
    ISNULL([SharepointId], 'Total') as 'Sharepoint', 
    Count([Id]) as 'Nombre de tickets' 
FROM [dbo].[Tickets]
GROUP BY ROLLUP([SharepointId])
ORDER BY GROUPING([SharepointId]), 2 DESC

GROUPING([SharepointId]) is 0 when the column is not grouped (so those rows sort first) and then it's 1 when the column is grouped (so that row goes last). This is especially useful when rolling up multiple columns.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Simple use case expression : 

order by (case when SharepointId is null then 1 else 0 end), [Nombre de tickets] desc;

Upvotes: 0

B3S
B3S

Reputation: 1051

Try this one

SELECT 
    ISNULL([SharepointId], 'Total') as [Sharepoint], 
    Count([Id]) as [Nombre de tickets]
FROM [dbo].[Tickets]
GROUP BY ROLLUP([SharepointId])
ORDER BY CASE WHEN [SharepointId] IS NULL THEN 'B' ELSE 'A' END, [Nombre de tickets] DESC

Upvotes: 1

Aswani Madhavan
Aswani Madhavan

Reputation: 816

Modify the Order By clause as below : 

CASE WHEN  ISNULL([SharepointId], 'Total') <>'Total' THEN  
Count([Id])END DESC, 1

Upvotes: 0

Related Questions