SQL GROUP BY

The GROUP BY clause groups records into summary rows.

GROUP BY returns one record for each group.

GROUP BY is used with aggregrates: COUNT, MAX, SUM, etc.

Example

#

List the number of products for each supplier, sorted high to low.

SELECT S.CompanyName, COUNT(P.Id) AS Products 
  FROM Supplier S
  JOIN Product P ON S.Id = P.SupplierId
 GROUP BY S.CompanyName
 ORDER BY COUNT(P.Id) DESC

Result: 29 records

CompanyName	Products
Pavlova, Ltd.	5
Plutzer Lebensmittelgroßmärkte AG	5
Specialty Biscuits, Ltd.	4
New Orleans Cajun Delights	4
Norske Meierier	3

SQL Is Null

SQL Having

Syntax

#

GROUP BY syntax.

SELECT column-names
  FROM table-name
 WHERE condition
 GROUP BY column-names

GROUP BY syntax with ORDER BY.

SELECT column-names
  FROM table-name
 WHERE condition
 GROUP BY column-names
 ORDER BY column-names

More Examples

#

GROUP BY with COUNT

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List the number of customers in each country.

SELECT Country, COUNT(Id) AS Customers
  FROM Customer
 GROUP BY Country

Result: 21 records.

Country	Customers
Argentina	3
Austria	2
Belgium	2
Brazil	9
Canada	3

GROUP BY with COUNT, ORDER BY

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List the number of customers in each country, sorted high to low.

SELECT Country, COUNT(Id) AS Customers 
  FROM Customer
 GROUP BY Country
 ORDER BY COUNT(Id) DESC

Result: 21 records.

Country	Customers
USA	13
France	11
Germany	11
Brazil	9
UK	7

GROUP BY with SUM, ORDER BY

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

Problem: List the total order amount for each customer, sorted high to low.

SELECT C.FirstName, C.LastName, 
       SUM(O.TotalAmount) AS Total 
  FROM [Order] O 
  JOIN Customer C ON O.CustomerId = C.Id
 GROUP BY C.FirstName, C.LastName
 ORDER BY SUM(O.TotalAmount) DESC

Result: 89 records.