SQL GROUP BY

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

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

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

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

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

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

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

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

• Our Sql HAVING Tutorial
• Our Sql PIVOT Reference
• Our Sql COUNT Function Reference