CODEWITHSUNDEEP
sqlsql-serversortinggroup-bysql-order-by
user3486647
user3486647

Reputation: 321

How to sort specific column when using GROUPING SETS in SQL Server?

If I remember correctly, in SQL Server, it is not recommended to use "ORDER BY" when there's already a GROUP BY GROUPING SETS.

I have two columns: [UPC#] & [Description] both are varchar.

My grouping sets is like this:

GROUP BY 
GROUPING SETS 
(
    ([UPC],[Description])
    ,()     
)

I don't have 'ORDER BY' but it's automatically sorting the Description column.

If I added a 3rd column, sum(Qty), then it doesn't sort by Description anymore. But if I added

ORDER BY [Description]

The grand total of sum(Qty) will be at the first row instead of the last.

Is there a way to sort the Description column and still let the grand total of sum(Qty) be at the last row instead?

Thanks.

***Edit 1**** This is my code as requested:

SELECT
    CASE WHEN [UPC] IS NULL THEN ''
    ELSE [UPC]
    END AS [UPC]

    ,   CASE WHEN [Description] IS NULL THEN ''
        ELSE [Description] 
        END AS [Description]

    ,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Total Count]

FROM 
(
    SELECT 
        [UPC]
        ,[Description]
        , sum([QtySold]) as [QtySold]
    FROM [JS_Data].[dbo].[View_ItemMovement_AllItems_withoutZero]
    WHERE 
        ([Description] LIKE '%drink%') 
        AND (SaleDate BETWEEN '2014-01-01' AND '2014-01-15')
        AND ( (StoreNumber = '1') OR (StoreNumber = '2') OR (StoreNumber = '3') OR (StoreNumber = '4') OR (StoreNumber = '6') OR (StoreNumber = '7') OR (StoreNumber = '8') )
    GROUP BY 
            [UPC]
            ,[Description]
) a

GROUP BY 
GROUPING SETS 
    (
        (
            [UPC]
            ,[Description]
        )
        ,()     
    )

ORDER BY [Description]

enter image description here

So how do I move the grand total 1396 to the last row?

Upvotes: 4

Views: 5415

Answers (2)

Sam
Sam

Reputation: 27

I realize this question is aging a bit, but just came across an example in an MCSA study guide today and I thought I would share. I've tested this and it works with a nearly identical grouping set arrangement.

...
GROUP BY GROUPING SETS 
(
    ([UPC],[Description])
    ,()     
)
ORDER BY GROUPING(UPC);

Upvotes: 2

King King
King King

Reputation: 63367

I think you should know about the grouping() function, it will return 1 if the passed-in column joined in some aggregate grouping and the current value is NULL. Of course we need to do some trick with the Order By clause, something like this:

--....
GROUP BY 
GROUPING SETS 
(
    (
        [UPC]
        ,[Description]
    )
    ,()     
)
ORDER BY 
      CASE WHEN
           -- this ensures the total row is always put at the end
           GROUPING([UPC]) = 1 AND GROUPING([Description]) = 1 THEN '1'
           ELSE '0' + [Description]
      END

I guess in this case you can even check for NULL instead of using GROUPING, but it's not safe (in case there is some actual NULL value such as in the Description).

Upvotes: 3

Related Questions