SQL GROUP BY Tutorial

Use the SQL GROUP BY Clause is to consolidate like values into a single row.  The group is a set of columns. The group by returns a single row from one or more within the query having the same column values. Its main purpose is this work alongside functions, such as SUM or COUNT, and provide a means to summarize values.

What is a SQL Group?

In SQL, groups are unique column value combinations. When a query has a GROUP BY, rather than returning every row that meets the filter condition, values are first grouped together. The rows returned are the unique combinations within the columns.

The overall syntax for GROUP BY is:

SELECT   colum1, column2, column3, .. 
FROM     table1 
GROUP BY column1, column2, column3,… 

For the lesson, we’ll focus on the Sales.SalesOrderDetail

When using SQL GROUP BY, keep these points in mind:

• The SQL GROUP BY does not guarantee the groups are returned in sorted order.
• You can use aggregate functions, such as SUM and COUNT to summarize data.
• The non summarized columns in your SELECT list must match those listed in the GROUP BY.

SQL GROUP BY and DISTINCT

In some ways GROUP BY is similar to using the DISTINCT Clause.

SELECT DISTINCT SalesOrderID, CarrierTrackingNumber 
FROM   Sales.SalesOrderDetail 

Return the same results as this SQL using SQL GROUP BY:

SELECT   SalesOrderID, CarrierTrackingNumber 
FROM     Sales.SalesOrderDetail 
GROUP BY SalesOrderID, CarrierTrackingNumber 

Here, we included the COUNT() function to determine how many rows make up each group:

/* Answer */
SELECT SalesOrderID, CarrierTrackingNumber, COUNT(1) as NumberofLines 
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber;

Ordering Grouping Results

Groups aren’t returned in sorted order. In order to do this, you need to include the ORDER BY clause. Add the ORDER BY clause to the end of the SQL statement.

Modify this statement so it sorts the groups by SalesOrderID and CarrierTrackingNumber.

/* Answer */
SELECT SalesOrderID, CarrierTrackingNumber, COUNT(1) as NumberofLines 
FROM Sales.SalesOrderDetail 
GROUP BY SalesOrderID, CarrierTrackingNumber 
ORDER BY SalesOrderID, CarrierTrackingNumber 

Summarizing Results with SQL GROUP BY

You can use these functions to summarize data within a group:

• COUNT() – Returns number of rows within group.
• MIN() – Return the minimum value found within the group.
• MAX() – Returns the maximum value found within the group.
• SUM() – Returns the sum of the value found with the group.
• AVG() – Returns the average value found within the group (SUM() / COUNT() )

Here are some tips to think about when using these functions. You can:

1. Use more than one function in your SQL.
2. Include the functions within Expressions.
3. Alias the expression.

/* Answer */
SELECT SalesOrderID
	 , CarrierTrackingNumber
	 , COUNT(1) AS NumberofLines
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
	   , CarrierTrackingNumber
ORDER BY SalesOrderID
	   , CarrierTrackingNumber; 

As a final example, here is how to calculate the AVG() function result’s using SUM() and COUNT().  Notice how thhe average is calculated using SUM() and COUNT(),

/* Answer */
SELECT SalesOrderID
	 , CarrierTrackingNumber
	 , SUM(LineTotal) / COUNT(1) AS CalcAvgLineTotal
	 , AVG(LineTotal) AS AvgLineTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
	   , CarrierTrackingNumber; 

SQL Group By Practice Questions

It’s important to practice! Use the sample database to answer these questions.

Write a query to return the average quantity ordered in the OrderDetails table?

/* Answer */
SELECT   JobTitle,
         Count(JobTitle) NumberOfJobs
FROM     HumanResources.Employee
GROUP BY JobTitle

Display the Min, Max, and Average Quantity ordered for each product in OrderDetails.

/* Answer */
SELECT   ProductID,
         Min(OrderQty) MinQty,
         Max(OrderQty) MaxQty,
         Avg(OrderQty) AvgQty
FROM     Sales.SalesOrderDetail
GROUP BY ProductID

Additional Resources

If you’re looking to learn more about using the SQL GROUP BY examples, then I would recommend these resources:

• SQL MAX Function
• SQL COUNT Function
• What is the Difference between ORDER and GROUP BY?
• Looking for a super SQL book? Check out SQL Queries for Mere Mortals.