This is the tenth, and final, article in a short series of SQL Server query challenges to test and expand your SQL Server querying skills. While none of these short articles is of overwhelming complexity, they will hopefully either teach you something new or help you to reactivate potentially dormant SQL skills in preparation for your next real-world challenge. You can see the rest of the articles on the Query Answers with SQL Server page.
In this article we will once again imagine that you are working for a small classic car retailer called Prestige Cars Ltd, using the data that is in the company database.
This time it is the CEO herself who wants a quick report to see sales by make and color including subtotals for all possible combinations of makes and sales. Your challenge is to come up with the SQL to satisfy her request. Here is one possible solution:
SELECT MakeName, Color, SUM(Cost) AS Cost FROM Data.Make MK INNER JOIN Data.Model MD ON MK.MakeID = MD.MakeID INNER JOIN Data.Stock ST ON ST.ModelID = MD.ModelID GROUP BY GROUPING SETS ((MakeName, Color), (MakeName), (Color), ()) ORDER BY MakeName, Color
Running this query gives the results that you can see in Figure 1. You might want to scroll down this list to see the subtotals for other makes.
Figure 1. Adding subtotals with grouping sets
How It Works
Of course, you need to understand the PrestigeCars database if you are going to comprehend this code – especially the FROM/JOIN clauses. To make your life easier, here in Figure 2 is the ERD (Entity Relationship Diagram) for this database:
Figure 2: The Prestige Cars database Entity Relationship Diagram
SQL does not limit you to displaying the grand total and the totals for each aggregation. It can also show any intermediate totals. In this way, SQL can help you see both the big picture and the interesting nuggets of detail.
The code that can deliver this mix of high-level aggregation and low-level details is, at its heart, a fairly simple aggregation query. It takes the Make, Model, and Stock tables and joins them before outputting the total cost for each make and color.
However, the GROUP BY clause is extended with a more complex GROUPING SETS clause that requests these items.
A grand total
This is done by adding the empty parentheses inside the GROUPING SETS clause.
Total for make
This is done by adding (MakeName)—in its own parentheses—inside the GROUPING SETS clause.
Total for color
This is done by adding (Color)—in its own parentheses—inside the GROUPING SETS clause.
For make and color. These are requested just as you would request them in a standard GROUP BY clause—by adding the Make and Color fields separated by a comma. However, because this aggregation is a GROUPING SETS clause, these field names must be inside their own set of parentheses.
It can take a little practice to appreciate exactly what the GROUPING SETS query has delivered. More specifically, you have to look closely at the NULLs in the various columns to see how the subtotals are created when applying grouping sets to an aggregate query. Put simply
- If all columns except the value are NULL, then the row is the grand total.
- If a column contains a NULL alongside other elements, you are looking at the total for those elements. For instance, if the MakeName column is NULL and the Color column contains “British Racing Green,” then the figure for that row is the total for the color “British Racing Green.” Equally, if the MakeName column contains “Alfa Romeo” and the Color column contains NULL, then the figure shown in the record is the total for the make “Alfa Romeo.”
Conceptually, the GROUPING SETS operator behaves in the way that is described in Figure 3.
Figure 3. Anatomy of a GROUPING SETS operator
Tricks and Traps
- You do not have to add an ORDER BY clause when using grouping sets; however, it does often make the final output easier to understand if you add this.
- You do not have to add the lowest level of aggregation to a GROUPING SETS clause if you do not want to see the subtotals for each combination of make and color. For instance, if you alter the GROUP BY clause in this example so that it reads
GROUP BY GROUPING SETS ((MakeName), (Color), ())
you only see the totals for Make, Color, and the grand total, as you can see in Figure 4.
Figure 4. Displaying the higher aggregation levels using a GROUPING SETS clause
That is it – you have seen a simple example of how to create subtotals and totals in aggregated queries using GROUPING SETS.
We hope that you have enjoyed this series of short articles.
This article is adapted from the book “Query Answers with SQL Server Volume II: In-Depth Querying” by Adam and Karine Aspin. Available as both a paperback and an eBook from all good resellers including Amazon, Kobo and iBooks, as well as all good bookstores.
The sample data is available either in the accompanying material for this article, or on the Tetras Publishing website at: www.tetraspublishing.com/sqlserver.
There are a number of articles in this series. You can see them all on the Query Answers page.