SQL GROUP BY Clause
What is the purpose of the GROUP BY clause?
The GROUP BY clause groups records into summary rows.
It returns one record for each group.
GROUP BY queries often include aggregates: COUNT, MAX, SUM, AVG, etc.
A GROUP BY clause can group by one or more columns.
The SQL GROUP BY syntax
The general syntax is
SELECT column-names FROM table-name WHERE condition GROUP BY column-names
The general syntax with ORDER BY is:
SELECT column-names FROM table-name WHERE condition GROUP BY column-names ORDER BY column-names
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SQL GROUP BY Examples
Problem: List the number of customers in each country.
SELECT COUNT(Id), Country FROM Customer GROUP BY Country
Result: 21 records.
Count | Country |
---|---|
3 | Argentina |
2 | Austria |
2 | Belgium |
9 | Brazil |
3 | Canada |
![]() |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
Problem: List the number of customers
in each country sorted high to low
in each country sorted high to low
SELECT COUNT(Id), Country FROM Customer GROUP BY Country ORDER BY COUNT(Id) DESC
Result: 21 records.
Count | Country |
---|---|
13 | USA |
11 | France |
11 | Germany |
9 | Brazil |
7 | UK |
![]() |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
Problem: List the total amount ordered
for each customer
for each customer
SELECT SUM(O.TotalAmount) AS SUM, C.FirstName, C.LastName FROM [Order] O JOIN Customer C ON O.CustomerId = C.Id GROUP BY C.FirstName, C.LastName ORDER BY SUM(O.TotalPrice) DESC
This query JOINs Order with Customer to obtain customer names
Result: 89 records.
Sum | FirstName | LastName |
---|---|---|
117483.39 | Horst | Kloss |
115673.39 | Jose | Pavarotti |
113236.68 | Roland | Mendel |
57317.39 | Patricia | McKenna |
52245.90 | Paula | Wilson |
34101.15 | Mario | Pontes |
32555.55 | Maria | Larsson |
![]() |