SQL GROUP BY Clause

What is the purpose of the GROUP BY clause?

The GROUP BY clause groups records into summary rows.
It returns one record for each group.

GROUP BY queries often include aggregates: COUNT, MAX, SUM, AVG, etc.
A GROUP BY clause can group by one or more columns.

The SQL GROUP BY syntax

The general syntax is

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

The general syntax with ORDER BY is:

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

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

SQL GROUP BY Examples

Problem: List the number of customers in each country.

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

Result: 21 records.

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

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

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

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

Result: 21 records.

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

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

Problem: List the total amount ordered
for each customer

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

This query JOINs Order with Customer to obtain customer names

Result: 89 records.

Sum	FirstName	LastName
117483.39	Horst	Kloss
115673.39	Jose	Pavarotti
113236.68	Roland	Mendel
57317.39	Patricia	McKenna
52245.90	Paula	Wilson
34101.15	Mario	Pontes
32555.55	Maria	Larsson