The GROUP BY statement groups rows with identical values into summary rows, such as finding the number of customers in each country.
It is commonly paired with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); |
Here is a portion of 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 |
Here is a portion of the “Customers” table in the Northwind sample database:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; |
The following SQL statement displays the count of customers in each country, sorted in descending order:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC; |
Here is a portion of 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 |
Here is a portion of the “Shippers” table:
ShipperID |
ShipperName |
1 |
Speedy Express |
2 |
United Package |
3 |
Federal Shipping |
The following SQL statement displays the count of orders sent by each shipper:
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName; |