SQL GROUP BY

SQL GROUP BY Explained

The GROUP BY clause groups records into summary rows.

GROUP BY returns one record for each group.

GROUP BY can group records by one or more columns.

GROUP BYs are used with aggregrates: COUNT, MAX, etc.

Example

#

Problem: 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

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

SQL GROUP BY COUNT

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List the number of customers in each country.
SELECT Country, COUNT(Id) AS Count 
  FROM Customer
 GROUP BY Country
Result: 21 records.
Country Count
Argentina 3
Austria 2
Belgium 2
Brazil 9
Canada 3

SQL GROUP BY 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 Count 
  FROM Customer
 GROUP BY Country
 ORDER BY COUNT(Id) DESC
Result:  21 records.
Country Count
USA 13
France 11
Germany 11
Brazil 9
UK 7

SQL GROUP BY 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

This query JOINs Order with Customer to obtain customer names.

Result: 89 records.
FirstName LastName Sum
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



Guides