Dec 22, 2012

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

    Choose :
  • OR
  • To comment
2 comments:
Write Comments
  1. National Football is one
    of a most popular sport in American,and many fans like to buy they favorite
    plays’ jerseys,now I introduce you an professional online store,wholesale all
    kinds nike nfl jerseys, http://www.mlbnfljerseysale.com/nfl-jerseys-c-28.html welcome to choose, http://www.mlbnfljerseysale.com/ncaa-jerseys-c-291.html

    ReplyDelete