sql server - SQL - Using CUBE grand total per row

Question

Here is my code:

SELECT
    ISNULL (CONVERT(VARCHAR, MONTH(PurchaseDate)), NULL) [Month],
    ISNULL (Brand, CASE 
                      WHEN MONTH(PurchaseDate) IS NOT NULL THEN 'Monthly SubTotal'
                      WHEN Brand IS NULL THEN 'Grand Total'
                      ELSE 'N/A'
                   END) [Brand], SUM(Price) [Total Amount]
FROM 
    [dbo].[Purchase_Items] 
GROUP BY 
    MONTH(PurchaseDate), Brand WITH CUBE

I want to change it to Grand Total on selected box. How to code it or change the string on it.

Answer 1

If you first get your data (a simpler version of what you have above) you can then use that as a data source to do conversions/updates as needed.

I'm using a CTE here, but you can do it with subqueries just as well.

WITH MonthTotals AS
    (SELECT
       MONTH(PurchaseDate) [Month],
       [Brand],
       SUM(Price) [Total Amount]
    FROM [dbo].[Purchase_Items] 
    GROUP BY MONTH(PurchaseDate), Brand WITH CUBE
    )
SELECT CONVERT(VARCHAR(2), mt.[Month]) AS [Month],
       CASE WHEN mt.[Month] IS NULL AND mt.[Brand] IS NULL THEN 'Grand Total'
            WHEN mt.[Month] IS NULL THEN 'Grand total for ' + mt.[Brand]
            WHEN mt.[Brand] IS NULL THEN 'Monthly total'
            ELSE mt.[Brand] END AS [Brand]
       [Total Amount]
  FROM MonthTotals mt;

Note though that CUBE is usually done in SQL Server like the following - it means you can select which columns you CUBE by (or rollup, etc)

GROUP BY CUBE(MONTH(PurchaseDate), Brand)

---

IMPORTANT UPDATE following @MartinSmith's comment below

Martin Smith gave the advice that I should use the GROUPING function. In reviewing that function, he is 100% correct (and thankyou Martin - this is my learning for today).

For reference, the GROUPING function indicates (with a 1 or 0) whether the row is an aggregate row or not (e.g., one of the rows added by ROLLUP/CUBE/GROUPING SETs).

I also made a mistake with subtotals for months - put it in the wrong column.

Therefore, the update should be the following (note also that I have included the 'original' vales from the CUBE for month and brand as well)

WITH MonthTotals AS
    (SELECT
       MONTH(PurchaseDate) [Month],
       [Brand],
       SUM(Price) [Total Amount],
       GROUPING(MONTH(PurchaseDate)) AS Agg_flag_Month,
       GROUPING([Brand]) AS Agg_flag_Brand
    FROM [dbo].[Purchase_Items] 
    GROUP BY CUBE(MONTH(PurchaseDate), Brand)
    )
SELECT  [Month] AS Orig_Month,
        [Brand] AS Orig_Brand,
        CASE WHEN Agg_flag_Month = 1 THEN 'Grand total for ' + mt.[Brand]
            ELSE CONVERT(VARCHAR(2), mt.[Month]) 
            END AS [Month],
       CASE WHEN Agg_flag_Month * Agg_flag_Brand = 1 THEN 'Grand Total'
            WHEN Agg_flag_Brand = 1 THEN 'Monthly total'
            ELSE mt.[Brand] 
            END AS [Brand],
       [Total Amount]
  FROM MonthTotals mt;