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.

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

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.

FirstName
LastName
Total

Horst
Kloss
117483.39

Jose
Pavarotti
115673.39

Roland
Mendel
113236.68

Patricia
McKenna
57317.39

Paula
Wilson
52245.90

Mario
Pontes
34101.15

Maria
Larsson
32555.55

You may also like

#