Flash cards
Review the key moves
What is the main idea behind SQL GROUP BY Statement?
Lesson checks
Practice each idea before moving on
Short Mimo-style checks built from this lesson's code, terms, and sequence.
Which statement best captures the main point of this lesson?
Complete the missing token from the example code.
___, aggregate_function(column2), column3, ...Put the learning moves in the order that makes the concept easiest to apply.
The SQL GROUP BY Statement
The GROUP BY statement is used to group rows that have the same values into summary rows, like "Find the number of customers in each country".
The GROUP BY statement is almost always used in conjunction with aggregate functions, like COUNT() , MAX() , MIN() , SUM() , AVG() , to perform calculations on each group.
GROUP BY Syntax
SELECT
column1, aggregate_function(column2), column3, ...
FROM
table_name
WHERE
condition
GROUP BY
column1
,
column3
ORDER BY
column_name
;Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno TaquerÃa | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Demo Database
Below is a selection from the "Orders" table in the Northwind sample database:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10248 | 90 | 5 | 1996-07-04 | 3 |
| 10249 | 81 | 6 | 1996-07-05 | 1 |
| 10250 | 34 | 4 | 1996-07-08 | 2 |
And a selection from the "Shippers" table:
| ShipperID | ShipperName |
|---|---|
| 1 | Speedy Express |
| 2 | United Package |
| 3 | Federal Shipping |
GROUP BY With JOIN Example
The following SQL returns the number of orders sent by each shipper:
Example
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM
Orders
LEFT JOIN Shippers
ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;