Error when SQL Server grand total comes out on first row instead of at table end

The following query gets row and column totals. When I add ORDER BY, the column total appears in the first row instead of the bottom row. What could be your suggested solution?

Select
   isnull (Definition_, 'GrandTotal') AS 'Definition_',
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 1 THEN OrderQty END),0) AS January,
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 2 THEN OrderQty END),0) AS Feburary, 
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 3 THEN OrderQty END),0) AS March,    
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 4 THEN OrderQty END),0) AS April,    
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 5 THEN OrderQty END),0) AS May,      
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 6 THEN OrderQty END),0) AS June,  
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 7 THEN OrderQty END),0) AS July,
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 8 THEN OrderQty END),0) AS August,
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 9 THEN OrderQty END),0) AS September,
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 10 THEN OrderQty END),0) AS October,
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 11 THEN OrderQty END),0) AS November,
   isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 12 THEN OrderQty END),0) AS December,
   isnull (SUM(OrderQty),0) AS GrandTotal,
    
    FROM Production.WorkOrder
    GROUP BY GROUPING SETS((DATEPART(YEAR, DueDate), Definition_, ProductID),
    ())order by ProductID;

Answer

you can use grouping which returns 1 if the column is part of aggregation and you can use it in your order by:

...
order by grouping(ProductID) 
        ,grouping(Definition_)
        ,grouping(DATEPART(YEAR, DueDate))
        ,ProductID;

Advertisement

Answer