Generating Subtotals using Subtotal(in a single query),Grouping

//GROUPING
/* The GROUPING function is used as an additional column in the query, and this function will render the output value as '1' for the grand total row and '0' for the rest If we omit the ROLL UP the GROUPING function will not produce the desired output.

The ROLLUP operator is useful in generating reports that contain subtotals and totals.

*/

//Sample Data

SELECT

CASE WHEN (GROUPING(Item) = 1) THEN 'ALL' ELSE ISNULL(Item, 'UNKNOWN') END AS Item,

CASE WHEN (GROUPING(Color) = 1) THEN 'ALL' ELSE ISNULL(Color, 'UNKNOWN') END AS Color,

SUM(Quantity) AS QtySum

FROM Inventory GROUP BY Item, Color WITH ROLLUP

/*Individual investment record
* A separate row for the total amount invested and total broker commission *amount under each scheme
* A separate row for the total amount invested and total broker commission *amount under each product
*A single row for the total amount invested and total broker commission amount *under all products.
*************************************************************************************/
//Method 1:
//Create a Sample Table and Data


DECLARE @GroupTest TABLE
(
Product_Name varchar(100),
Scheme_Name varchar(100),
Invest_Amount numeric(18,2),
Broker_Commission numeric(18,2),
Issue_Date datetime
)




declare @cnt int, @idx int, @index int
set @cnt = 1
set @idx = 1
set @index = 1


while @cnt <=100 begin insert into @GroupTest values ( 'Product'+CAST(@idx AS VARCHAR), 'Scheme'+CAST(@index AS VARCHAR), ((@cnt * @idx / @index)* 1524.15),((@cnt * @idx / @index)* 124.32), GETDATE() ) if @cnt % 10 = 0 set @idx = @idx + 1 set @index = @index + 1 if @cnt % 5 = 0 set @index = 1 set @cnt = @cnt + 1 end //Individual investment record
SELECT
Product_Name,
Scheme_Name,
Invest_Amount,
broker_commission,
'0 Detail' AS Comment
FROM
@GroupTest ORDER BY Product_Name,Scheme_Name,Comment


/*A separate row for the total amount invested and total broker commission amount under each scheme */


SELECT
Product_Name,
Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'1 Scheme_Total'
FROM @GroupTest
GROUP BY Product_Name,Scheme_Name
ORDER BY Product_Name,Scheme_Name, Comment


/* A separate row for the total amount invested and total broker commission *amount under each product */
SELECT
Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'2 Product_Total' AS Comment
FROM @GroupTest
GROUP BY Product_Name
ORDER BY Product_Name, Scheme_Name, Comment


/*A single row for the total amount invested and total broker commission amount under all products. */
SELECT
'ZProductTotal' As Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'3 Grand_Total' AS Comment
FROM @GroupTest
ORDER BY Product_Name,Scheme_Name, Comment


/*Desired Output*/
SELECT
Product_Name,
Scheme_Name,
Invest_Amount,
broker_commission,
'0 Detail' AS Comment
FROM @GroupTest
UNION ALL
SELECT
Product_Name,
Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'1 Scheme_Total' as Comment
FROM @GroupTest
GROUP BY Product_Name,Scheme_Name
UNION ALL
SELECT
Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'2 Product_Total' AS Comment
FROM @GroupTest
GROUP BY Product_Name
UNION ALL
SELECT
'ZProductTotal' As Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'3 Grand_Total' AS Comment
FROM @GroupTest
ORDER BY Product_Name,Scheme_Name, Comment

// Method 2:

// Using Grouping function

SELECT

Product_Name,

Scheme_Name,

Invest_Amount,

broker_commission,

-1 AS GrandTotal

FROM @GroupTest

UNION ALL

SELECT

ISNULL(Product_Name,'ZProductTotal'),

ISNULL(Scheme_Name,'ZTotal'),

SUM(Invest_Amount) AS 'Invest_Amount',

SUM(broker_commission) AS 'Broker_Commission',

GROUPING(Product_Name) 'GrandTotal'

FROM @GroupTest

GROUP BY Product_Name,Scheme_Name

WITH ROLLUP ORDER BY Product_Name,Scheme_Name, GrandTotal

//Fig 1. With Grouping // Fig 2. With grouping and without ISNULL function