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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas