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:

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

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.

/*modify this statement*/ SELECT SalesOrderID, CarrierTrackingNumber, COUNT(1) as NumberofLines FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, CarrierTrackingNumberSELECT TOP 10 * FROM Person;
/* 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:

  1. You can use more than one function in your SQL.
  2. You can use the functions within Expressions.
  3. You can alias the expression.
SELECT SalesOrderID, SUM(LineTotal) as OrderTotal, AVG(LineTotal) as AverageLineTotal, COUNT(LineTotal) as NumberOfLines FROM Sales.SalesOrderDetail GROUP BY SalesOrderID ORDER BY SalesOrderID SELECT TOP 10 * FROM Person;
/* 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.

SELECT SalesOrderID, CarrierTrackingNumber, SUM(LineTotal)/COUNT(1) CalcAvgLineTotal, AVG(LineTotal) AvgLineTotal FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, CarrierTrackingNumberSELECT TOP 10 * FROM Person;
/* Answer */
SELECT SalesOrderID, CarrierTrackingNumber,  
       SUM(LineTotal)/COUNT(1) CalcAvgLineTotal, AVG(LineTotal) 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:

>