SQLGROUP BY Statement
The SQL GROUP BY Statement
TheGROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
TheGROUP BY statement is often used with aggregate functions (COUNT(),MAX(),MIN(),SUM(),AVG()) to group the result-set by one or more columns.
GROUP BY Syntax
SELECTcolumn_name(s)
FROMtable_name
WHEREcondition
GROUP BYcolumn_name(s)
ORDER BYcolumn_name(s);
FROMtable_name
WHEREcondition
GROUP BYcolumn_name(s)
ORDER BYcolumn_name(s);
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 |
SQL GROUP BY Examples
The following SQL statement lists the number of customers in each country:
The following SQL statement lists the number of customers in each country, sorted high to low:
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
Try it Yourself »FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
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 statement lists 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;
Try it Yourself »LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
