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.

The Challenge

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.

Aggregate totals

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.

The Series

There are a number of articles in this series. You can see them all on the Query Answers page.