datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to generate Subtotal, Total and Grand Total columns?

Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate the architecting of subtotal, total and grand total queries.

Grand Total definition- the sum of the TOTALs of several groups of numbers.

—SQL grand total simple, quick syntax - isnull is used to make report readable

SELECT   isnull(convert(VARCHAR,ProductID),'GRAND TOTAL') AS ProductID,

         sum(Quantity)                                    AS 'Total In Inventory'

FROM     AdventureWorks2008.Production.ProductInventory

WHERE    ProductID >= 800

GROUP BY ProductID WITH ROLLUP

ORDER BY ProductID;

GO

/* Partial results

ProductID         Total In Inventory

996               970

997               153

998               155

999               194

GRAND TOTAL       65709

*/

------------

-- SQL sales subtotal, total, grand total - simple logic - month, year, all times

SELECT   isnull(convert(VARCHAR,Year(OrderDate)),'GRAND TOTAL') AS YEAR,

         CASE

           WHEN YEAR(OrderDate) IS NOT NULL

           THEN isnull(convert(VARCHAR,Month(OrderDate)),'YEARLY TOTAL')

           ELSE ''

         END AS MONTH,

-- SQL currency formatting

         '$'+CONVERT(varchar,SUM(TotalDue),1)     AS [MONTHLY SALES SUBTOTAL]

FROM     AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY Year(OrderDate),

         Month(OrderDate) WITH ROLLUP

ORDER BY YEAR,

         MONTH

/*  Partial results

 

YEAR              MONTH                   MONTHLY SALES SUBTOTAL

2004              4                       $4,722,890.74

2004              5                       $6,518,825.23

2004              6                       $6,728,034.99

2004              7                       $56,178.92

2004              YEARLY TOTAL            $32,196,912.42

GRAND TOTAL                               $140,707,584.82

*/

------------

First complex example: GROUP BY..WITH ROLLUP is used to generate subtotal at the Quarter level, total at the Year level and grand total for all times AdventureWorks Cycle in business. While the output is easily understandable, the total query is on the complex side.

------------

-- Microsoft SQL grand total, total, subtotal generation with T-SQL GROUP BY ROLLUP

------------

-- MSSQL grouping function

USE AdventureWorks2008;

 

SELECT   TotalType = CASE

                       WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4)) = 1

                         THEN 'GRAND TOTAL'

                       WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +

                            CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1)) = 1  

                         THEN 'TOTAL - YY'

                       WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +

                            CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) +

                            right('0' + CONVERT(VARCHAR,month(OrderDate)),2)) = 1

                         THEN 'SUBTOTAL - QQ'

                       ELSE ''

                     END,

         [Year] = ISNULL(left(CONVERT(CHAR(8),OrderDate,112),4),''),

         [Quarter] = ISNULL(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +

                     CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1),

                            ''),

         [Month] = COALESCE(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +

                   CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +

                   CONVERT(VARCHAR,month(OrderDate)),2),

                            ''),

         Purchases = '$' + CONVERT(VARCHAR,SUM(SubTotal),1)

FROM     Purchasing.PurchaseOrderHeader

GROUP BY left(CONVERT(CHAR(8),OrderDate,112),4),

         left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +

              CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1),

         left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +

              CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +

              CONVERT(VARCHAR,month(OrderDate)),2) WITH ROLLUP

ORDER BY GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4)),

         [Year],

         GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +

         CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1)),

         [Quarter],

         GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +

         CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +

         CONVERT(VARCHAR,month(OrderDate)),2)),

         [Month]

/* Results 

 

TotalType         Year  Quarter     Month Purchases

                  2001  2001Q2      2001Q205    $103,895.82

SUBTOTAL - QQ     2001  2001Q2                  $103,895.82

TOTAL - YY        2001                          $103,895.82

                  2002  2002Q1      2002Q101    $299,239.98

                  2002  2002Q1      2002Q102    $700,406.63

                  2002  2002Q1      2002Q103    $328,572.46

SUBTOTAL - QQ     2002  2002Q1                  $1,328,219.07

                  2002  2002Q2      2002Q204    $646,975.84

                  2002  2002Q2      2002Q205    $298,745.12

                  2002  2002Q2      2002Q206    $106,491.00

SUBTOTAL - QQ     2002  2002Q2                  $1,052,211.96

                  2002  2002Q3      2002Q307    $655,405.08

                  2002  2002Q3      2002Q308    $116,119.65

                  2002  2002Q3      2002Q309    $499,186.65

SUBTOTAL - QQ     2002  2002Q3                  $1,270,711.38

                  2002  2002Q4      2002Q410    $186,121.65

                  2002  2002Q4      2002Q411    $388,868.98

SUBTOTAL - QQ     2002  2002Q4                  $574,990.63

TOTAL - YY        2002                          $4,226,133.03

                  2003  2003Q1      2003Q103    $131,485.79

SUBTOTAL - QQ     2003  2003Q1                  $131,485.79

                  2003  2003Q2      2003Q205    $641,097.40

                  2003  2003Q2      2003Q206    $924,127.80

SUBTOTAL - QQ     2003  2003Q2                  $1,565,225.19

                  2003  2003Q3      2003Q307    $105,014.51

                  2003  2003Q3      2003Q308    $98,304.85

                  2003  2003Q3      2003Q309    $5,100,678.73

SUBTOTAL - QQ     2003  2003Q3                  $5,303,998.10

                  2003  2003Q4      2003Q410    $3,449,392.92

                  2003  2003Q4      2003Q411    $1,775,204.15

                  2003  2003Q4      2003Q412    $3,424,287.10

SUBTOTAL - QQ     2003  2003Q4                  $8,648,884.17

TOTAL - YY        2003                          $15,649,593.25

                  2004  2004Q1      2004Q101    $4,266,022.62

                  2004  2004Q1      2004Q102    $4,137,584.18

                  2004  2004Q1      2004Q103    $4,704,085.56

SUBTOTAL - QQ     2004  2004Q1                  $13,107,692.37

                  2004  2004Q2      2004Q204    $5,172,365.20

                  2004  2004Q2      2004Q205    $5,729,967.42

                  2004  2004Q2      2004Q206    $5,522,963.61

SUBTOTAL - QQ     2004  2004Q2                  $16,425,296.24

                  2004  2004Q3      2004Q307    $6,710,223.01

                  2004  2004Q3      2004Q308    $6,824,989.46

                  2004  2004Q3      2004Q309    $743,151.66

SUBTOTAL - QQ     2004  2004Q3                  $14,278,364.13

                  2004  2004Q4      2004Q410    $1,020.00

SUBTOTAL - QQ     2004  2004Q4                  $1,020.00

TOTAL - YY        2004                          $43,812,372.74

GRAND TOTAL                                     $63,791,994.84

*/

------------

Second complex example: summary query generates Subtotal, Total and Grand Total for monthly, yearly and all times sales. In data warehousing, the dates are referred to as date (or time) dimension with 3 levels.

 

------------

-- GRAND TOTAL (all times), TOTAL (year), SUBTOTAL (month) mssql select query

------------

-- Microsoft T-SQL derived table rpt generates the report - select from select

-- The outer query does the final filtering and sorting

USE AdventureWorks;

 

SELECT *

FROM

(

  SELECT YY=COALESCE(CONVERT(varchar,YEAR(OrderDate)),''),

         MM=COALESCE(LEFT(CONVERT(varchar,OrderDate,111),7),''),

              ORDERS = COUNT(*),

              SALES = '$'+CONVERT(varchar,SUM(TotalDue),1),

          GRPMM = CASE WHEN

 GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 0

                       AND  GROUPING(YEAR(OrderDate)) = 1

                          THEN 'SUBTOTAL' ELSE '' END, 

          GRPYY= CASE WHEN GROUPING(YEAR(OrderDate)) = 0

AND  GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1

                         THEN 'TOTAL' ELSE '' END,

          GRPALL = CASE WHEN

                   GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1

                        AND GROUPING(YEAR(OrderDate)) = 1

                                  THEN 'GRAND TOTAL' ELSE '' END

 

  FROM Sales.SalesOrderHeader

  GROUP BY YEAR(OrderDate), LEFT(CONVERT(varchar,OrderDate,111),7)

WITH CUBE

) rpt

WHERE

       GRPMM != '' OR GRPYY !='' OR GRPALL !=''

ORDER BY

            CASE WHEN GRPALL!= '' THEN 3

                 WHEN GRPYY != '' THEN 2

            ELSE 1 END,

            YY, MM

GO

/* Partial results

 

YY    MM          ORDERS      SALES             GRPMM       GRPYY GRPALL

      2004/04     2127        $4,268,473.54     SUBTOTAL         

      2004/05     2386        $5,813,557.45     SUBTOTAL         

      2004/06     2374        $6,004,155.77     SUBTOTAL         

      2004/07     976         $56,178.92        SUBTOTAL         

2001              1379        $12,693,250.63                TOTAL

2002              3692        $34,463,848.44                TOTAL

2003              12443       $47,171,489.55                TOTAL

2004              13950       $28,887,306.04                TOTAL

                  31464       $123,215,894.65                     GRAND TOTAL

*/

------------

Third complex example: the MonthlyOrderSummary stored procedure will generate Account Number Total (subtotal), Day Total (total)and Month Total (grand total) columns.

------------

-- T-SQL grand total, total, subtotal stored procedure
------------

-- T-SQL create stored procedure

USE AdventureWorks2008;

GO

CREATE PROCEDURE MonthlyOrderSummary 

-- ALTER PROCEDURE MonthlyOrderSummary

                @Year  INT,

                @Month INT

AS

  BEGIN

    SELECT   soh.AccountNumber,

             PO = isnull(soh.PurchaseOrderNumber,''),

                  convert(CHAR(10),soh.OrderDate,111) AS 'Order Date',

             soh.TotalDue,

             CASE

               WHEN SalesOrderID = (

               SELECT   TOP 1 SalesOrderID

               FROM     Sales.SalesOrderHeader

               WHERE    convert(CHAR(10),OrderDate,111) =

                        convert(CHAR(10),soh.OrderDate,111)

                 ORDER BY SalesOrderID DESC) THEN (SELECT '$' +

                       convert(VARCHAR,sum(TotalDue),1)

                       FROM   Sales.SalesOrderHeader

                       WHERE  SalesOrderID <= soh.SalesOrderID

                       AND convert(CHAR(11),OrderDate,111) =

                       convert(CHAR(10),soh.OrderDate,111))

               ELSE ' '

             END AS 'Day Total',

             CASE

               WHEN SalesOrderID = (

               SELECT   TOP 1 SalesOrderID

               FROM     Sales.SalesOrderHeader

               WHERE    year(OrderDate) = @Year

                   AND month(OrderDate) = @Month

                 ORDER BY OrderDate DESC) THEN (SELECT '$' +

                 convert(VARCHAR,sum(TotalDue),1)

                      FROM   Sales.SalesOrderHeader

                      WHERE  year(OrderDate) = @Year

                         AND month(OrderDate) = @Month)

               ELSE ' '

             END AS 'Month Total'

    FROM     Sales.SalesOrderHeader soh

    WHERE    year(OrderDate) = @Year

             AND month(OrderDate) = @Month

    ORDER BY SalesOrderID

  END

 

GO

 

-- MSSQL execute stored procedure with 2 parameters

EXEC MonthlyOrderSummary   2003,  7

GO

 

 

/* Partial results - TotalDue is    SUBTOTAL

                     Day Total is   TOTAL

                     Month Total is GRAND TOTAL

                    

AccountNumber     PO    Order Date  TotalDue    Day Total   Month Total

10-4030-011069          2003/07/30  2563.589          

10-4030-011101          2003/07/30  2696.189          

10-4030-026306          2003/07/30  830.2307          

10-4030-024421          2003/07/30  2703.9903         

10-4030-022398          2003/07/30  596.689     $23,476.44 

10-4030-019435          2003/07/31  2715.3497              $4,681,520.64

*/

------------

Related articles:

SQL Server: Calculating Running Totals, Subtotals and Grand Total Without a Cursor

SQL Server 2008 Grouping Sets

SQL Server Summaries with ROLLUP and CUBE

 

 

Exam Prep 70-461
Exam 70-461