Identify Subtotals with GROUP BY
If you are iterating through the query result to create a report or chart of the data, you have to distinguish between aggregated data and subtotal rows. You can use GROUPING(fieldName) to do this. Using GROUPING(fieldName) is more important for interpreting your results when you have more than one field in your GROUP BY ROLLUP or GROUP BY CUBE clause. It is the best way to differentiate between aggregated data and subtotals.
This example query returns subtotals for combinations of the LeadSource and Rating fields. GROUPING(LeadSource) indicates if the row is an aggregated row for the LeadSource field, and GROUPING(Rating) does the same for the Rating field.
SELECT LeadSource, Rating,
GROUPING(LeadSource) grpLS, GROUPING(Rating) grpRating,
COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(LeadSource, Rating)
The following table shows the query results.
LeadSource | Rating | grpLS | grpRating | cnt | Comment |
---|---|---|---|---|---|
Web | null | 0 | 0 | 5 | Five leads with LeadSource = Web with no Rating |
Web | Hot | 0 | 0 | 1 | One lead with LeadSource = Web with Rating = Hot |
Web | Warm | 0 | 0 | 1 | One lead with LeadSource = Web with Rating = Warm |
Web | null | 0 | 1 | 7 | Subtotal of seven leads with LeadSource = Web (grpRating = 1 indicates that result is grouped by the Rating field) |
Phone Inquiry | null | 0 | 0 | 4 | Four leads with LeadSource = Phone Inquiry with no Rating |
Phone Inquiry | null | 0 | 1 | 4 | Subtotal of four leads with LeadSource = Phone Inquiry (grpRating = 1 indicates that result is grouped by the Rating field) |
Partner Referral | null | 0 | 0 | 4 | Four leads with LeadSource = Partner Referral with no Rating |
Partner Referral | null | 0 | 1 | 4 | Subtotal of four leads with LeadSource = Partner Referral (grpRating = 1 indicates that result is grouped by the Rating field) |
Purchased List | null | 0 | 0 | 7 | Seven leads with LeadSource = Purchased List with no Rating |
Purchased List | null | 0 | 1 | 7 | Subtotal of seven leads with LeadSource = Purchased List (grpRating = 1 indicates that result is grouped by the Rating field) |
null | null | 1 | 1 | 22 | Grand total of 22 leads (grpRating = 1 and grpLS = 1 indicates this is the grand total) |