SQL Group By

The SQL GROUP BY Clause is used to output a row across specified column values.  Its main purpose is this work along side functions, such as SUM or COUNT, and provide a means to summarize values.

In SQL, groups are unique column value combinations. When a query has a GROUP BY, rather than returning every rows 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,… 
ORDER BY column1, column2, … 

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

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

If you use GROUP BY, then column in the select list must be in the GROUP BY statement, excluding those in functions, such as SUM and COUNT.

The results of your query aren’t sorted by the SQL GROUP BY columns. Add and ORDER BY clause to your query to do this.

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 

The key difference that when using GROUP BY, you can also summarize values. 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;

Notice that the groups aren’t returned in sorted order. In order to do this, you need to include the ORDER BY clause. The ORDER BY clause is added at the very 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 SUM and COUNT are used in the same expression to calculate the average.

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

Additional Resources

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

• Summarize your Results with the GROUP BY clause 
• What is the Difference between ORDER and GROUP BY? 
• Looking for a super SQL book? Check out SQL Queries for Mere Mortals.