Subtotal in SQL Query

Generate subtotals on different columns and grand total on a resultset in SQL is a very common requirement

In this article, I will show you how to achieve this easily with the help of Grouping function Rollup provided by Microsoft in SQL Server.

Example

Lets take Sales Table with fields year, month, prod_id, qty

Now, assume we have to find out the quantity of items sold product wise, month wise and year wise and total. Now let's write a query to calculate the same within the single query with the help of Grouping function Rollup.

DECLARE @sales TABLE(
    year INT, month INT, prod_id VARCHAR(10), qty INT
)

INSERT INTO @sales
SELECT 2011, 1, 'P1', 10 UNION ALL
SELECT 2011, 1, 'P1', 50 UNION ALL
SELECT 2011, 1, 'P2', 55 UNION ALL
SELECT 2011, 1, 'P2', 80 UNION ALL
SELECT 2011, 2, 'P1', 50 UNION ALL
SELECT 2011, 2, 'P1', 70 UNION ALL
SELECT 2011, 2, 'P2', 60 UNION ALL
SELECT 2011, 2, 'P2' ,20 UNION ALL
SELECT 2012, 1, 'P1', 50 UNION ALL
SELECT 2012, 1, 'P1', 25 UNION ALL
SELECT 2012, 1, 'P2', 15 UNION ALL
SELECT 2012, 1, 'P2', 20 UNION ALL
SELECT 2012, 2, 'P1', 50 UNION ALL
SELECT 2012, 2, 'P1', 70 UNION ALL
SELECT 2012, 2, 'P2', 60 UNION ALL
SELECT 2012, 2, 'P2', 20

SELECT [Year]   = COALESCE(CAST(year AS VARCHAR), 'Grand Total')
     , [Month]  = CASE WHEN month IS NULL AND year IS NOT NULL
                  THEN 'Total for Year:' + CAST(year AS VARCHAR)
                  ELSE CAST(month AS VARCHAR) END
     , [Product]= CASE WHEN prod_id IS NULL AND month IS NOT NULL
                  THEN 'Total for Month:' + CAST(month AS VARCHAR)
                  ELSE CAST(prod_id AS VARCHAR) END
     , [Qty]    = SUM(qty)       
FROM  @sales
GROUP BY year, month, prod_id
WITH ROLLUP

OUTPUT

In the above query, we have used COALESCE. Refer below post to understand COALESCE in detail

• COALESCE in SQL Server