This article continues the series about logical query processing, which describes the logical, or conceptual, interpretation of queries. Part 1 provided an overview of the topic and also a sample database called TSQLV4. It also provided sample queries which I’ll referred to as simple sample query and complex sample query. I’ll use the same sample database and queries in this article. Parts 2, 3, 4 and 5 covered the logical query processing aspects of the first major query clause—the FROM clause. Part 6 covered the second major query clause—the WHERE clause. In this article I continue the series by describing the logical query processing aspects of the third and fourth major query clauses—GROUP BY and HAVING, respectively.
Logical query processing flow chart including the GROUP BY and HAVING clauses
The optional GROUP BY clause is processed in the third logical query processing step, and the optional HAVING clause in the fourth. The third step, which processes the GROUP BY clause, operates on the virtual table returned by the second step, which processes the WHERE clause. The third step arranges the rows from the input table in groups based on the grouping set that you define in the GROUP BY clause. Then the fourth step filters groups based on the predicate that you specify in the HAVING clause. Only groups for which the predicate evaluates to true are returned, whereas groups for which the predicate evaluates to false or unknown are discarded. Figure 1 shows a flow chart with the logical query processing of the FROM, WHERE, GROUP BY and HAVING clauses.
Figure 1: Logical query processing flow chart – GROUP BY and HAVING
I’ll use simple sample query and complex sample query from part 1 in the series to demonstrate the inputs and outputs of the steps that process the GROUP BY and HAVING clauses. The following code includes the GROUP BY and HAVING clauses of our simple sample query:
SELECT ...
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE C.country = N'Spain'
GROUP BY C.custid
HAVING COUNT( O.orderid ) <= 3;
Following is the state of the data after handling the WHERE clause (step 2), and before handling the GROUP BY clause (step 3):
C.custid O.orderid ----------- ----------- 8 10326 8 10801 8 10970 22 NULL 29 10366 29 10426 29 10568 29 10887 29 10928 30 10303 30 10550 30 10629 30 10872 30 10874 30 10888 30 10911 30 10948 30 11009 30 11037 69 10281 69 10282 69 10306 69 10917 69 11013
This input set consists of 24 rows (showing here only the columns C.custid and O.orderid). The grouping set defined in our query is (C.custid), and since there are five distinct C.custid values in the input set, the result of the GROUP BY phase organizes the input rows in five groups, like so:
|---------|------------------| | Groups | Rows | |---------|---------|--------| |C.custid |C.custid |orderid | |---------|---------|--------| | |8 |10326 | |8 |8 |10801 | | |8 |10970 | |---------|---------|--------| |22 |22 |NULL | |---------|---------|--------| | |29 |10366 | | |29 |10426 | |29 |29 |10568 | | |29 |10887 | | |29 |10928 | |---------|---------|--------| | |30 |10303 | | |30 |10550 | | |30 |10629 | | |30 |10872 | |30 |30 |10874 | | |30 |10888 | | |30 |10911 | | |30 |10948 | | |30 |11009 | | |30 |11037 | |---------|---------|--------| | |69 |10281 | | |69 |10282 | |69 |69 |10306 | | |69 |10917 | | |69 |11013 | |---------|---------|--------|
The HAVING phase applies the predicate COUNT( O.orderid ) <= 3 to filter groups. Only two out of the five input groups qualify, so the output of this step consists of only those two groups:
|---------|------------------| | Groups | Rows | |---------|---------|--------| |C.custid |C.custid |orderid | |---------|---------|--------| | |8 |10326 | |8 |8 |10801 | | |8 |10970 | |---------|---------|--------| |22 |22 |NULL | |---------|---------|--------|
The following code has the GROUP BY and HAVING parts of our complex sample query:
SELECT ...
FROM Sales.Customers AS C
LEFT OUTER JOIN
( Sales.Orders AS O
INNER JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
AND O.orderdate >= '20160101' )
ON C.custid = O.custid
CROSS APPLY ( VALUES( CONCAT(C.country, N'.' + C.region, N'.' + C.city),
OD.qty * OD.unitprice * (1 - OD.discount) )
) AS A(custlocation, val)
WHERE A.custlocation IN (N'Spain.Madrid', N'France.Paris', N'USA.WA.Seattle')
GROUP BY C.custid, A.custlocation
HAVING COUNT( DISTINCT O.orderid ) <= 3;
The WHERE phase returned the following result, which consists of 27 rows:
C.custid A.custlocation O.orderid OD.productid A.val --------- --------------- ---------- ------------- ------------- 8 Spain.Madrid 10970 52 224.0000000 22 Spain.Madrid NULL NULL NULL 57 France.Paris NULL NULL NULL 69 Spain.Madrid 10917 30 25.8900000 69 Spain.Madrid 10917 60 340.0000000 69 Spain.Madrid 11013 23 90.0000000 69 Spain.Madrid 11013 42 56.0000000 69 Spain.Madrid 11013 45 190.0000000 69 Spain.Madrid 11013 68 25.0000000 74 France.Paris 10907 75 108.5000000 74 France.Paris 10964 18 375.0000000 74 France.Paris 10964 38 1317.5000000 74 France.Paris 10964 69 360.0000000 74 France.Paris 11043 11 210.0000000 89 USA.WA.Seattle 10861 17 1638.0000000 89 USA.WA.Seattle 10861 18 1250.0000000 89 USA.WA.Seattle 10861 21 400.0000000 89 USA.WA.Seattle 10861 33 87.5000000 89 USA.WA.Seattle 10861 62 147.9000000 89 USA.WA.Seattle 10904 58 198.7500000 89 USA.WA.Seattle 10904 62 1725.5000000 89 USA.WA.Seattle 11032 36 665.0000000 89 USA.WA.Seattle 11032 38 6587.5000000 89 USA.WA.Seattle 11032 59 1650.0000000 89 USA.WA.Seattle 11066 16 52.3500000 89 USA.WA.Seattle 11066 19 386.4000000 89 USA.WA.Seattle 11066 34 490.0000000
This set is used as the input to the GROUP BY phase, which in this query groups the rows by the grouping set (C.custid, A.custlocation). The step organizes the rows from the input set in the following six groups:
|---------|----------------------------------------------------------------| | Groups | Rows | |---------|---------|---------------|----------|-------------|-------------| |C.custid |C.custid |A.custlocation |O.orderid |OD.productid |A.val | |---------|---------|---------------|----------|-------------|-------------| |8 |8 |Spain.Madrid |10970 |52 |224.0000000 | |---------|---------|---------------|----------|-------------|-------------| |22 |22 |Spain.Madrid |NULL |NULL |NULL | |---------|---------|---------------|----------|-------------|-------------| |57 |57 |France.Paris |NULL |NULL |NULL | |---------|---------|---------------|----------|-------------|-------------| | |69 |Spain.Madrid |10917 |30 |25.8900000 | | |69 |Spain.Madrid |10917 |60 |340.0000000 | |69 |69 |Spain.Madrid |11013 |23 |90.0000000 | | |69 |Spain.Madrid |11013 |42 |56.0000000 | | |69 |Spain.Madrid |11013 |45 |190.0000000 | | |69 |Spain.Madrid |11013 |68 |25.0000000 | |---------|---------|---------------|----------|-------------|-------------| | |74 |France.Paris |10907 |75 |108.5000000 | | |74 |France.Paris |10964 |18 |375.0000000 | |74 |74 |France.Paris |10964 |38 |1317.5000000 | | |74 |France.Paris |10964 |69 |360.0000000 | | |74 |France.Paris |11043 |11 |210.0000000 | |---------|---------|---------------|----------|-------------|-------------| | |89 |USA.WA.Seattle |10861 |17 |1638.0000000 | | |89 |USA.WA.Seattle |10861 |18 |1250.0000000 | | |89 |USA.WA.Seattle |10861 |21 |400.0000000 | | |89 |USA.WA.Seattle |10861 |33 |87.5000000 | | |89 |USA.WA.Seattle |10861 |62 |147.9000000 | | |89 |USA.WA.Seattle |10904 |58 |198.7500000 | |89 |89 |USA.WA.Seattle |10904 |62 |1725.5000000 | | |89 |USA.WA.Seattle |11032 |36 |665.0000000 | | |89 |USA.WA.Seattle |11032 |38 |6587.5000000 | | |89 |USA.WA.Seattle |11032 |59 |1650.0000000 | | |89 |USA.WA.Seattle |11066 |16 |52.3500000 | | |89 |USA.WA.Seattle |11066 |19 |386.4000000 | | |89 |USA.WA.Seattle |11066 |34 |490.0000000 | |---------|---------|---------------|----------|-------------|-------------|
The query then filters groups based on the HAVING predicate COUNT( DISTINCT O.orderid ) <= 3, returning five out of the original six groups:
|---------|----------------------------------------------------------------| | Groups | Rows | |---------|---------|---------------|----------|-------------|-------------| |C.custid |C.custid |A.custlocation |O.orderid |OD.productid |A.val | |---------|---------|---------------|----------|-------------|-------------| |8 |8 |Spain.Madrid |10970 |52 |224.0000000 | |---------|---------|---------------|----------|-------------|-------------| |22 |22 |Spain.Madrid |NULL |NULL |NULL | |---------|---------|---------------|----------|-------------|-------------| |57 |57 |France.Paris |NULL |NULL |NULL | |---------|---------|---------------|----------|-------------|-------------| | |69 |Spain.Madrid |10917 |30 |25.8900000 | | |69 |Spain.Madrid |10917 |60 |340.0000000 | |69 |69 |Spain.Madrid |11013 |23 |90.0000000 | | |69 |Spain.Madrid |11013 |42 |56.0000000 | | |69 |Spain.Madrid |11013 |45 |190.0000000 | | |69 |Spain.Madrid |11013 |68 |25.0000000 | |---------|---------|---------------|----------|-------------|-------------| | |74 |France.Paris |10907 |75 |108.5000000 | | |74 |France.Paris |10964 |18 |375.0000000 | |74 |74 |France.Paris |10964 |38 |1317.5000000 | | |74 |France.Paris |10964 |69 |360.0000000 | | |74 |France.Paris |11043 |11 |210.0000000 | |---------|---------|---------------|----------|-------------|-------------|
If you’re wondering why the HAVING predicate computes a distinct count of order IDs, that’s because the query joins the Sales.Orders table with the Sales.OrderDetails table, resulting in a row per order line—not a row per order. The HAVING filter in our query is supposed to return only groups that have an order count—not an order line count—that is less than or equal to three.
Detail is discarded
The GROUP by phase organizes the rows in groups, and from that point in the logical processing of the query, your expressions don’t have direct access to the detailed state of the data anymore. This applies to all subsequent phases, which are the steps that process the HAVING (step 4), SELECT (step 5) and ORDER BY (step 6) clauses of the query. If in those clauses you want to refer to a column from the input tables, you can do so directly only if that column is part of the query’s grouping set. In order to refer to a column that is not part of the query’s grouping set, that column must be contained in a group aggregate function. For example, the following query is invalid since in the SELECT list you refer to the column orderid, and this column is neither part of your grouping set, nor is it contained in a group aggregate function:
SELECT custid, orderid FROM Sales.Orders GROUP BY custid;
You get the following error:
Msg 8120, Level 16, State 1, Line 48 Column 'Sales.Orders.orderid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The reasoning behind this restriction is that within a single group you can have rows with different order ID values, but the result will consist of only one row per group. However, applying an aggregate function to a column (or an expression based on a column) guarantees that there will be only one result value per group. For example, the following query computes the maximum order ID per customer, and therefore is a perfectly valid query:
SELECT custid, MAX(orderid) AS lastorder FROM Sales.Orders GROUP BY custid;
Theoretically SQL could allow exceptions to the above rule in cases where based on constraints it could infer that certain columns can have only one distinct value per group, even if they’re not part of the grouping set. However, SQL doesn’t support such implied inference. As an example, consider the following query:
SELECT C.custid, C.companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
GROUP BY C.custid;
There’s a primary key constraint defined on the custid column in the Sales.Customers table, enforcing the uniqueness of the custid values. This means that all other columns in the table are functionally dependent on the custid column. So, in the result of the join between Sales.Customers and Sales.Orders, all rows with the same custid value will always share the same values in all of the rest of the columns. So in the above query, SQL could theoretically infer that since C.custid is part of the grouping set, there will always be precisely one C.companyname value associated with each group. However, as mentioned, SQL (and the same applies to T-SQL) doesn’t support this sort of inference, and you get the following error when trying to execute this query:
Msg 8120, Level 16, State 1, Line 56 Column 'Sales.Customers.companyname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
There are two main workarounds that SQL supports. One is to add the C.companyname column to the grouping set, like so (call this Query 1):
SELECT C.custid, C.companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
GROUP BY C.custid, C.companyname;
Another is to apply an artificial aggregate function like MIN or MAX to the C.companyname column, like so (call this Query 2):
SELECT C.custid, MAX(C.companyname) AS companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
GROUP BY C.custid;
If you’re wondering which solution is more efficient, examine the query plans shown in Figure 2:
Figure 2: Plans for Query 1 and Query 2
Observe that in the plan for Query 1, the optimizer applied a pushdown of the grouping and aggregation work right after scanning the data from the Orders table, prior to applying the join with the Customers table. It figures that since the join is an equijoin, grouping by O.custid is the same as grouping by C.custid, and then the pushdown can be applied while preserving the original query meaning. This leaves the join with less work to do compared to processing the grouping and aggregation after the join. In the plan for Query 2, the optimizer doesn’t realize that the aggregate against the C.companyname column is artificial, and handles the grouping and aggregation after the join. Personally, I find Query 1 also to be the clearer and more natural of the two. Since Query 1 is both more efficient and more natural, it’s generally my preferred choice between the two.
Aggregating data without discarding the detail
Compared to getting the raw, or detailed, state of the data, grouping gives you new insights in the form of aggregated calculations. However, at the same time grouping also discards the detail, as demonstrated earlier. For example, consider the following query:
SELECT custid, SUM(val) AS custtotal FROM Sales.OrderValues GROUP BY custid;
The source view has a row per order, but since the query groups the data by custid, the output contains a row per customer, with the customer’s total order values:
custid custtotal ------- ---------- 1 4273.00 2 1402.95 3 7023.98 ... (89 row(s) affected)
But what if you don’t want to discard the detail? For example, suppose that you want to return the detailed information about the order and in addition you want to compute the percentage of the current order value out of the customer total. You need the percentage calculation to divide the detailed order value by the aggregate of all order values for the same customer. You could achieve this by writing a query that groups the data by custid and computes the customer total, then define a table expression based on that query, and use an outer query to join the table expression with the Sales.OrderValues view to match the detail with the aggregates. But this adds complexity to the code. Alternatively, you can compute the aggregate using a window function instead of a group function, like so:
SELECT custid, orderid, val, val / SUM(val) OVER(PARTITION BY custid) AS pctcust FROM Sales.OrderValues;
Unlike grouping, which discards the detail, windowing doesn’t discard the detail. Th aggregate is computed against a window of rows that is derived from the underlying query result set, and is defined by the function’s OVER clause. Since the underlying query result is established only when logical query processing reaches the SELECT phase (after processing the FROM, WHERE, GROUP BY and HAVING phases), window functions are allowed only in the SELECT and ORDER BY clauses of the query. If you specify an empty OVER clause, the function operates on the complete underlying query result, and you would get the grand total. If you add a window partition clause, like in the above query, the function operates on a restricted partition, which in our example restricts the rows to only the ones where the custid value is the same as in the current row. In other words, our window function computes the customer total. The window function returns its result without discarding the detail, and hence you can combine detail elements from the row with the result of the window aggregate function. Our query divides the current order value by the customer total to compute the percentage: val / SUM(val) OVER(PARTITION BY custid). This query generates the following output:
custid orderid val pctcust ------- -------- -------- ----------------------------- 1 10643 814.50 0.19061549262813011935408378 1 10692 878.00 0.20547624619705125204774163 1 10702 330.00 0.07722911303533816990404867 1 10835 845.80 0.19794055698572431546922536 1 10952 471.20 0.11027381230985256260238708 1 11011 933.50 0.21846477884390358062251345 2 10926 514.40 0.36665597491001104814854413 2 10759 320.00 0.22809080865319505328058733 2 10625 479.75 0.34195801703553227128550554 2 10308 88.80 0.06329519940126162728536298 3 10365 403.20 0.05740335251524064704056674 3 10507 749.06 0.10664324215046170404813225 3 10535 1940.85 0.27631770022124208781915666 3 10677 813.37 0.11579901992887223482982582 3 10573 2082.00 0.29641314468435274587911696 3 10682 375.50 0.05345971941833547362036907 3 10856 660.00 0.09396382108149510676283246 ... (830 row(s) affected)
Curiously, grouping and windowing aren't mutually exclusive. You can apply window functions against grouped data. Just remember that grouping happens in step 3 in logical query processing, and windowing happens post grouping, in the SELECT (step 5) or ORDER BY (step 6) phases. The basic rule that you want to remember is that unlike group aggregate functions which you can apply to detail elements as their inputs, window functions can only be applied to elements that would have otherwise normally been allowed in the SELECT clause. For instance, suppose that you want to write a grouped query where you compute customer total order values, and in addition, the percent of the customer total out of the grand total. You might attempt to use the following query:
SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(val) OVER() AS pct FROM Sales.OrderValues GROUP BY custid;
The reference to the val column in the group aggregate SUM functions is perfectly valid, but the reference to the val column in the window aggregate SUM function is invalid, just like such a reference would have been invalid directly in the SELECT clause. You get the following error:
Msg 8120, Level 16, State 1, Line 137 Column 'Sales.OrderValues.val' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
You might try an alternative where you apply the window function to the alias custtotal:
SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(custtotal) OVER() AS pct FROM Sales.OrderValues GROUP BY custid;
But this attempt also fails since such a reference would have been invalid directly in the SELECT clause due to the set-based treatment of expressions in the same logical phase. You get the following error:
Msg 207, Level 16, State 1, Line 145 Invalid column name 'custtotal'.
What is valid, surprisingly (or not), is to apply the window function to the group function, like so:
SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(SUM(val)) OVER() AS pct FROM Sales.OrderValues GROUP BY custid;
This time, the query runs successfully and you get the following output:
custid custtotal pct ------- ---------- --------- 1 4273.00 0.003375 2 1402.95 0.001108 3 7023.98 0.005549 ... (89 row(s) affected)
Similarly, suppose that you wanted to group the data from the Sales.OrderValues view by custid and orderdate, and in addition to computing the daily total, you want to compute the running total values from the beginning of the customer’s activity until the current date. As shown in the previous example, you can combine grouping and windowing to achieve this task, like so:
SELECT custid, orderdate, SUM(val) AS daytotal,
SUM(SUM(val)) OVER(PARTITION BY custid
ORDER BY orderdate
ROWS UNBOUNDED PRECEDING) AS runtotal
FROM Sales.OrderValues
GROUP BY custid, orderdate;
This query generates the following output:
custid orderdate daytotal runtotal ------- ---------- --------- --------- 1 2015-08-25 814.50 814.50 1 2015-10-03 878.00 1692.50 1 2015-10-13 330.00 2022.50 1 2016-01-15 845.80 2868.30 1 2016-03-16 471.20 3339.50 1 2016-04-09 933.50 4273.00 2 2014-09-18 88.80 88.80 2 2015-08-08 479.75 568.55 2 2015-11-28 320.00 888.55 2 2016-03-04 514.40 1402.95 3 2014-11-27 403.20 403.20 3 2015-04-15 749.06 1152.26 3 2015-05-13 1940.85 3093.11 3 2015-06-19 2082.00 5175.11 3 2015-09-22 813.37 5988.48 3 2015-09-25 375.50 6363.98 3 2016-01-28 660.00 7023.98 ... (823 row(s) affected)
HAVING versus WHERE
The HAVING clause serves a similar filtering purpose to the WHERE clause, where it filters data based on a predicate. The key difference between the two is that the WHERE clause filters rows before grouping, whereas the HAVING clause filters whole groups after grouping. So at the group level, the HAVING clause determines whether to keep the group or discard it based on the outcome of the predicate (if true, keep, if false or unknown, discard). As an example, suppose that you need to query the Sales.OrderValues view, and filter only orders that were placed on or after May 1st, 2016. You want to group the remaining orders by employee, and filter only groups having three orders or fewer. For qualifying groups, you want to return the employee ID and the order count. Here, the filter against the orderdate column has to be applied in the WHERE clause because it’s supposed to be treated as a row filter: WHERE orderdate >= '20160501'. Since orderdate is not part of the query’s grouping set, you can’t refer to it in the HAVING clause, unless it is contained within an aggregate function. But then it’s not like you want to filter groups where the outcome of an aggregate against the orderdate column satisfies some condition; you want to filter rows representing orders placed on or after a certain date. Conversely, after grouping, you want to apply a group filter that keeps only employee groups having three or fewer orders. Since this filter involves an aggregate calculation of the count of orders and needs to be applied at the group level, it has to be specified in the HAVING clause: HAVING COUNT(*) <= 3. Here’s the complete solution query:
SELECT empid, COUNT(*) AS numorders FROM Sales.OrderValues WHERE orderdate >= '20160501' GROUP BY empid HAVING COUNT(*) <= 3;
This query generates the following output:
empid numorders ----------- ----------- 2 2 4 2 7 2 8 3
If you need to apply a filter based on a column that is part of the query’s grouping set, e.g., empid > 0 in our query, you can choose whether to apply it as a row filter in the WHERE clause or as a group filter in the HAVING clause. The outcome is the same. For most people, in such a case it is more natural to apply the filter in the WHERE clause. SQL Server’s optimizer knows this and very likely will create the same plan in both cases.
GROUP BY ALL
T-SQL supports a nonstandard feature called GROUP BY ALL. It’s a pretty interesting feature, but you should be aware that the official documentation for SQL Server has a note advising to refrain from using it since Microsoft is planning to drop support for it at some point. Here I wanted to describe the logical query processing aspects of this feature and provide a recommended alternative.
In essence, the GROUP BY ALL option preserves empty groups that were filtered out by the WHERE clause. Any aggregate that you apply to those groups operates on an empty set. A COUNT(*) aggregate against such groups returns 0. As an example, consider the following query (call it Query 3):
SELECT empid, COUNT(*) AS numorders FROM Sales.OrderValues WHERE orderdate >= '20160501' GROUP BY ALL empid HAVING COUNT(*) <= 3;
The filter in the WHERE clause eliminates completely all rows for customers 3, 5, 6 nd 9. Earlier you saw that the result of a similar query without the GROUP BY ALL option didn’t return the groups for these customers. But since the GROUP BY ALL option preserves empty groups, the output of this query includes the groups for these customers with a count of 0:
empid numorders ----------- ----------- 2 2 3 0 4 2 5 0 6 0 7 2 8 3 9 0
As mentioned, Microsoft announced their intention to drop support for this feature in the future. Here’s the alternative to the previous query that should keep working in the future (call this Query 4):
SELECT empid, COUNT(tokeep) AS numorders
FROM Sales.OrderValues
CROSS APPLY ( VALUES( CASE WHEN orderdate >= '20160501' THEN 1 END ) )
AS A(tokeep)
GROUP BY empid
HAVING COUNT(tokeep) <= 3;
Instead of filtering the rows in the WHERE clause, the query uses a CASE expression based on the same predicate to determine whether to keep the value (tokeep column is 1) or to ignore it (tokeep column is NULL). You then apply your aggregate calculation to the tokeep column. Not only that this solution uses only supported elements that are likely to stay supported, it is also more efficient than the GROUP BY ALL solution. Figure 3 shows the query plans for Query 3 (GROUP BY ALL solution) and Query 4 (solution with CASE expression) using SQL Sentry’s plan explorer.
Figure 3: Plans for Query 3 and Query 4
The plan for the GROUP BY ALL solution (Query 3) repeats the scanning of the input data twice—once (the top branch of the plan) to return all rows without applying the WHERE filter along with a constant flag NULL, and a second time (bottom branch of the plan) with applying the WHERE filter along with a constant flag 0. The plan then concatenates the results, groups the rows by empid, computes the aggregates COUNT(flag), and finally applies the HAVING filter. Conversely, the plan for the solution based on the CASE expression (Query 4) scans the input data only once.
Grouping sets
Traditional group queries compute aggregates for a single grouping set (a single set of expressions that you group by). However, sometimes you need to write queries that produce aggregates for multiple grouping sets, usually for reporting purposes. For example, suppose that you want to query the Sales.Orders table, and compute daily, monthly, yearly, and grand order counts. You could achieve this by writing a separate grouped query for each grouping set, and then unify the results, like so:
WITH C AS
(
SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
FROM Sales.Orders
CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
AS A(orderyear, ordermonth, orderday)
GROUP BY orderyear, ordermonth, orderday
UNION ALL
SELECT orderyear, ordermonth, NULL AS orderday, COUNT(*) AS numorders
FROM Sales.Orders
CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate)) )
AS A(orderyear, ordermonth)
GROUP BY orderyear, ordermonth
UNION ALL
SELECT orderyear, NULL AS ordermonth, NULL AS orderday, COUNT(*) AS numorders
FROM Sales.Orders
CROSS APPLY ( VALUES(YEAR(orderdate)) ) AS A(orderyear)
GROUP BY orderyear
UNION ALL
SELECT NULL AS orderyear, NULL AS ordermonth, NULL AS orderday, COUNT(*) AS numorders
FROM Sales.Orders
)
SELECT orderyear, ordermonth, orderday, numorders
FROM C
ORDER BY
CASE WHEN orderyear IS NOT NULL THEN 0 ELSE 1 END, orderyear,
CASE WHEN ordermonth IS NOT NULL THEN 0 ELSE 1 END, ordermonth,
CASE WHEN orderday IS NOT NULL THEN 0 ELSE 1 END, orderday;
Observe that the code uses NULLs as place holders for elements that are not part of the current grouping set but are relevant in other grouping sets. The expressions in the ORDER BY clause guarantee hierarchical presentation order (daily counts followed by month total, monthly counts followed by year total, and finally the grand total). This query generates the following output, shown here in abbreviated form:
orderyear ordermonth orderday numorders ----------- ----------- ----------- ----------- 2014 7 4 1 2014 7 5 1 2014 7 8 2 2014 7 9 1 2014 7 10 1 2014 7 11 1 2014 7 12 1 2014 7 15 1 2014 7 16 1 2014 7 17 1 2014 7 18 1 2014 7 19 2 2014 7 22 1 2014 7 23 1 2014 7 24 1 2014 7 25 1 2014 7 26 1 2014 7 29 1 2014 7 30 1 2014 7 31 1 2014 7 NULL 22 ... 2016 4 29 3 2016 4 30 4 2016 4 NULL 74 2016 5 1 3 2016 5 4 3 2016 5 5 4 2016 5 6 4 2016 5 NULL 14 2016 NULL NULL 270 NULL NULL NULL 830 (507 row(s) affected)
SQL (and so does T-SQL) Supports a much more elegant solution for our need using a clause called GROUPING SETS, which you specify in the GROUP BY clause, and in which you list multiple grouping sets, like so:
SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
FROM Sales.Orders
CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
AS A(orderyear, ordermonth, orderday)
GROUP BY GROUPING SETS
(
(orderyear, ordermonth, orderday),
(orderyear, ordermonth),
(orderyear),
()
)
ORDER BY
GROUPING(orderyear), orderyear,
GROUPING(ordermonth), ordermonth,
GROUPING(orderday), orderday;
The GROUPING function returns a 0 for an input element if it’s part of the grouping set and 1 if it’s not (when it’s an aggregate). In our query it simplifies the expressions in the ORDER BY clause that guarantee hierarchical presentation ordering. From a logical query processing perspective, the GROUPING SETS clause allows you to define multiple grouping sets, and as a result associate each row returned from the WHERE pahse possibly with multiple groups instead of just one.
Back to the last query, when you need to compute all grouping sets that represent leading combination in a hierarchy, like our time hierarchy, instead of explicitly listing all those grouping sets in the GROUPING SETS clause, you can use an abbreviated syntax with the ROLLUP clause, like so:
SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
FROM Sales.Orders
CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
AS A(orderyear, ordermonth, orderday)
GROUP BY ROLLUP(orderyear, ordermonth, orderday)
ORDER BY
GROUPING(orderyear), orderyear,
GROUPING(ordermonth), ordermonth,
GROUPING(orderday), orderday;
This ROLLUP clause is equivalent to the previous query’s GROUPING SETS clause. It defines all grouping sets that represent leading combination of the input expressions.
For more information about grouping sets, see Grouping Sets Part 1 and Grouping Sets Part 2.
Conclusion
In this article I covered the logical query processing aspects of the GROUP BY and HAVING clauses. I provided the logical query processing flow chart with the FROM, WHERE, GROUP BY and HAVING clauses. I explained that grouping discards the detail and provided an alternative in the form of windowing, which doesn’t discard the detail. I explained the difference between the WHERE and HAVING predicates. I also covered the proprietary GROUP BY ALL option and provided an alternative that is more efficient and that is likely to be supported in the future, unlike GROUP BY ALL. Finally, I showed how to define multiple grouping sets in a single query using the GROUPSING SETS and ROLLUP clauses. Next month I’ll continue the discussion about logical query processing, covering the SELECT and ORDER BY clauses.
