Wow. Did I mess up on this one. Let me correct myself before someone else has to.
The answer to Sunil's question about why only the grand total line got a 1 indicator is quite simple. The GROUPING column was defined to indicate rows added by ROLLUP for a total of all Product_Name subtotals. To get an indcator for those subtotals, we'd have to add another column, GROUPING(Scheme_Name), which will indicate rows added by ROLLUP for totals of all Scheme_Names in a Product_Name.
SELECT Product_Name,
Scheme_Name,
Invest_Amount,
broker_commission,
-1 AS GrandTotal,
-1 as ProductTotal --<=== Added this
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',
GROUPING(Scheme_Name)'ProductTotal' --<==== added this
FROM @GroupTest
GROUP BY Product_Name,
Scheme_Name
WITH ROLLUP
ORDER BY Product_Name,
Scheme_Name,
GrandTotal
From the article:
Grouping separates the NULL values that are returned by ROLL UP from normal null values. In the above example (Method 2) the Totals and the Grand Total rows are actually generated by the ROLL UP operation. ROLL UP adds the NULL value for the summary row. Grouping separates this NULL by indicating 1 against the row. Hence in the above output (Method 2) the Grand Total summary row is indicated by a 1.
Here one question comes in the mind that why only one row is indicated by 1? The Scheme_Total and Product_Total is also aggregated but it is not indicated by 1. I have not yet received any satisfactory answer for this. I will post the answer here once I get it.
Let's start over with what GROUPING does. MSDN tells us that GROUPING is "... an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP."
This is wrong. Let me strike it out.....
Now we can answer the question as to why only one row gets the GROUPING indcator of 1 by recognizing that it is the only row that's inserted into the result set by the ROLLUP. The Product_Total amounts are indeed aggregated, but that's done by the GROUP BY, not by the ROLLUP.