An aggregate function used in a simple SELECT statement that has only a FROM clause and a WHERE clause produces a single summary value for all rows in a column.
If you want to generate summary values for a column based on groups of rows, use aggregate functions in SELECT statements with the GROUP BY clause. Use the HAVING clause with the GROUP BY clause to restrict the groups of rows that are returned in the result set.
After this lesson, you will be able to:
- Organize summary data for a column by using aggregate functions with the GROUP BY and HAVING clauses
Estimated lesson time: 50 minutes
Use the GROUP BY clause on columns or expressions to organize rows into groups and to summarize those groups. For example, Figure F.1 shows a SELECT statement that uses the SUM aggregate function and the GROUP BY clause to determine the quantity of each product that was ordered.
Compare the result sets in Figure F.1. The table on the left lists all the rows in the OrderHist table. The table on the top right uses the GROUP BY clause to group all orders by ProductID and present the total quantity that is ordered for each product. The table on the bottom right uses the GROUP BY clause and the WHERE clause to further restrict the number of rows returned.
TIP
The OrderHist table is created in the Northwind database specifically for the examples in this lesson and Lesson 3. If you want to execute the examples in these lessons, you can create the OrderHist table by executing the script in the C:\SQLDW\Exercise\AppF\ordhist.SQL script file.
Figure F.1 Using the GROUP BY clause
Partial Syntax
SELECT select_list
FROM table_ source[ WHERE search_condition ]
[ GROUP BY [ALL] group_by_expression [, n]]
[HAVING search_condition ]
When you use the GROUP BY clause, consider the following facts and guidelines:
Example 1
This example returns information about orders from the OrderHist table. The query groups the orders for each product ID and calculates the total quantity ordered for that product. The total quantity is calculated with the SUM aggregate function, which displays one value for each product in the result set.
USE Northwind SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM OrderHist GROUP BY ProductID |
Result
ProductID Total_Quantity ----------- -------------- 1 15 2 35 3 45 (3 row(s) affected) |
Example 2
This example adds a WHERE clause to the query in Example 1. The query restricts the rows to product ID 2 and then groups these rows and calculates the total quantity ordered. Compare this result set to that in Example 1.
USE Northwind SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM OrderHist WHERE ProductID = 2 GROUP BY ProductID |
Result
ProductID Total_Quantity ----------- -------------- 2 35 (1 row(s) affected) |
Example 3
This example summarizes information from the [Order Details] table. The query groups the orders by product ID and calculates the total quantity ordered for each product. This example does not include a WHERE clause and, therefore, returns a total for each product ID.
USE Northwind SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM [Order Details] GROUP BY ProductID |
Result
ProductID Total_Quantity ----------- -------------- 61 603 3 328 32 297 47 485 38 623 (77 row(s) affected) |
Example 4
This example adds a WHERE clause to the query in Example 3. The query returns only rows that have a product ID of less than 5. Compare this result set with the result set in Example 3.
CAUTION
The WHERE clause must be listed before the GROUP BY clause in the SELECT statement or SQL Server will return an error.
USE Northwind SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM [Order Details] WHERE ProductID < 5 GROUP BY ProductID |
Result
ProductID Total_Quantity ----------- -------------- 1 828 2 1057 3 328 4 453 (4 row(s) affected) |
In this exercise, you will open a script that contains a query that includes the GROUP BY clause.
Result
Your result will look similar to the following result set:
CategoryID Total_Quantity ----------- -------------- 1 9532 2 5298 (2 row(s) affected) |
Use the HAVING clause on columns or expressions to set conditions on the groups included in a result set. The HAVING clause sets conditions on the GROUP BY clause in much the same way that the WHERE clause sets conditions for the rows returned by the SELECT statement.
The WHERE clause determines which rows are grouped. The HAVING clause determines which groups are returned.
Figure F.2 shows how the HAVING clause limits the result set to products for which the total quantity ordered is at least 30.
Figure F.2 Using the GROUP BY clause with the HAVING clause
When you use the HAVING clause, consider the following facts and guidelines:
Example 1
This example lists each product from the OrderHist table that has orders of 30 or more units.
USE Northwind SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM OrderHist GROUP BY ProductID HAVING SUM(Quantity) >= 30 |
Result
ProductID Total_Quantity ----------- -------------- 2 35 3 45 (2 row(s) affected) |
Example 2
This example lists the product ID and quantity for products that have orders for more than 1,200 units in the [Order Details] table.
USE Northwind SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM [Order Details] GROUP BY ProductID HAVING SUM(Quantity) > 1200 |
Result
ProductID Total_Quantity ----------- -------------- 59 1496 56 1263 60 1577 31 1397 (4 row(s) affected) |
In this exercise, you will write queries that include the GROUP BY and the HAVING clauses. The C:\SQLDW\Exercise\AppF\groupbya.SQL script file contains a completed script for this exercise.
USE Northwind SELECT CategoryID, SUM(Quantity) AS Total_Quantity FROM [Order Details] JOIN Products ON [Order Details].ProductID = Products.ProductID GROUP BY CategoryID ORDER BY CategoryID |
Result
Your result will look similar to the following result set:
CategoryID Total_Quantity ----------- -------------- 1 9532 2 5298 3 7906 4 9149 5 4562 6 4199 7 2990 8 7681 (8 row(s) affected) |
USE Northwind SELECT OrderID, SUM(Quantity) AS Total_Quantity FROM [Order Details] GROUP BY OrderID |
Result
Your result will look similar to the following partial result set:
OrderID Total_Quantity ----------- -------------- 10248 27 10249 49 10250 60 11076 50 11077 72 (830 row(s) affected) |
USE Northwind SELECT OrderID, SUM(Quantity) AS Total_Quantity FROM [Order Details] GROUP BY OrderID HAVING SUM(Quantity) >= 250 |
Result
Your result will look similar to the following result set:
OrderID Total_Quantity ----------- -------------- 10515 286 10612 263 10658 255 10678 280 10847 288 10895 346 10990 256 11030 330 (8 row(s) affected) |
The GROUP BY clause allows you to specify columns and expressions by which the rows of a result set of a SELECT query are grouped into summary rows. The expressions in the select list of a query that has a GROUP BY clause must be aggregate functions or appear in the GROUP BY list in order for summary rows to be generated. The HAVING clause restricts the summary rows that are included in the result set.