GROUP BY CUBE
You can use GROUP BY CUBE with aggregate functions, such as SUM() and COUNT(fieldName).
[GROUP BY CUBE (fieldName[,...])]
A query with a GROUP BY CUBE clause returns the same aggregated data as an equivalent query with a GROUP BY clause. It also returns additional subtotal rows for each combination of fields specified in the comma-separated grouping list, as well as a grand total. You can include up to three fields in a GROUP BY CUBE clause.
The following query returns subtotals of accounts for each combination of Type and BillingCountry:
SELECT Type, BillingCountry,
GROUPING(Type) grpType, GROUPING(BillingCountry) grpCty,
COUNT(id) accts
FROM Account
GROUP BY CUBE(Type, BillingCountry)
ORDER BY GROUPING(Type), GROUPING(BillingCountry)
The following table shows the query results. The query uses ORDER BY GROUPING(Type), GROUPING(BillingCountry) so that the subtotal and grand total rows are returned after the aggregated data rows. This is not necessary, but it can help you when you are iterating through the query results in your code.
Type | BillingCountry | grpType | grpCty | accts | Comment |
---|---|---|---|---|---|
Customer - Direct | null | 0 | 0 | 6 | Six accounts with Type = Customer - Direct with BillingCountry = null |
Customer - Channel | USA | 0 | 0 | 1 | One account with Type = Customer - Channel with BillingCountry = USA |
Customer - Channel | null | 0 | 0 | 2 | Two accounts with Type = Customer - Channel with BillingCountry = null |
Customer - Direct | USA | 0 | 0 | 1 | One account with Type = Customer - Direct with BillingCountry = USA |
Customer - Channel | France | 0 | 0 | 1 | One account with Type = Customer - Channel with BillingCountry = France |
null | USA | 0 | 0 | 1 | One account with Type = null with BillingCountry = USA |
Customer - Channel | null | 0 | 1 | 4 | Subtotal of four accounts with Type = Customer - Channel (grpCty = 1 indicates that result is grouped by the BillingCountry field) |
Customer - Direct | null | 0 | 1 | 7 | Subtotal of seven accounts with Type = Customer - Direct (grpCty = 1 indicates that result is grouped by the BillingCountry field) |
null | null | 0 | 1 | 1 | Subtotal of one account with Type = null (grpCty = 1 indicates that result is grouped by the BillingCountry field) |
null | France | 1 | 0 | 1 | Subtotal of one account with BillingCountry = France (grpType = 1 indicates that result is grouped by the Type field) |
null | USA | 1 | 0 | 3 | Subtotal of three accounts with BillingCountry = USA (grpType = 1 indicates that result is grouped by the Type field) |
null | null | 1 | 0 | 8 | Subtotal of eight accounts with BillingCountry = null (grpType = 1 indicates that result is grouped by the Type field) |
null | null | 1 | 1 | 12 | Grand total of 12 accounts (grpType = 1 and grpCty = 1 indicates this is the grand total) |
You can use these query results to present a cross-tabular reports of the results.
Type/BillingCountry | USA | France | null | Total |
---|---|---|---|---|
Customer - Direct | 1 | 0 | 6 | 7 |
Customer - Channel | 1 | 1 | 2 | 4 |
null | 1 | 0 | 0 | 1 |
Total | 3 | 1 | 8 | 12 |