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

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.
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



Guides


vsn 3.1