When you want to identify the "scheme totals"-rows in your result set you need to add another GROUPING() column, this time specifying the Scheme_Name column for its parameter.
ISNULL(Product_Name,'ZProductTotal') as Product_name,
ISNULL(Scheme_Name,'ZTotal') as Scheme_Name,
SUM(Invest_Amount) AS [Invest_Amount],
SUM(broker_commission) AS [Broker_Commission],
GROUPING(Scheme_Name) AS [IsSchemeTotal],
GROUPING(Product_Name) AS [IsGrandTotal]
GROUP BY Product_Name,Scheme_Name
ORDER BY Product_Name,Scheme_Name, IsSchemeTotal, IsGrandTotal
An excerpt of the output is now:
Product_name Scheme_Name Invest_Amount Broker_Commission IsSchemeTotal IsGrandTotal
The first row in the excerpt shows the figures for Product9 in Scheme5, both grouping columns are 0, indicating this row is not a (sub-)totals row. On the 2nd row the IsSchemeTotal column returns 1, indicating this rows holds the sub-total for all schemes for Product9. On the 3rd row you see both IsSchemeTotal and IsGrandTotal set to 1, indicating this row holds the figures for both all schemes and all products, i.e. the grand total.
As an extra note: if you change the order by clause to
ORDER BY IsGrandTotal, Product_Name, IsSchemeTotal, Scheme_Name, you'l always get the 'normal' rows before the subtotals before the grand totals.
Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]
If you don't have time to do it right, when will you have time to do it over?