Contents
SQL GROUP BY Statement: Main Tips
- The GROUP BY statement is used with aggregate functions (MAX, MIN, COUNT, AVG, SUM) to group the result-set by one or more columns.
Syntax of SQL GROUP BY
Groups the result-set by one or more columns
Example
SELECT mycolumn_name(s)
FROM mytable_name
WHERE condition
GROUP BY mycolumn_name(s)
ORDER BY mycolumn_name(s);
Demo Database
This is demo example from the "Developers" table in the database:
| ID | Name | City | Country |
|---|---|---|---|
| 1 | Tom Kurkutis | New York | USA |
| 2 | Ana Fernandez | London | UK |
| 3 | Antonio Indigo | Paris | France |
| 4 | Aarav Kaelin | Delhi | India |
| 5 | Andrew Tumota | Miami | USA |
And the demo example from the "Clients" table in the database:
| ID | Name | Contact | Address | City | Postal_Code | Country |
|---|---|---|---|---|---|---|
| 1 | Ben Choplinks | Ben Choplink | Obeesre Str. 51 | Rome | 11207 | Italy |
| 2 | Donald Rich | Donald Richario | Avda. de la Confgfstitución 4122 | Tallin | 17021 | Estonia |
| 3 | Lilly Smilkins | Lilly Smilkin | Matadsderos 2312 | Eguero | 14023 | Mexico |
| 4 | Brandinina | Tom Hitchins | 110 Hanegover Sq. | London | WB2 2DP | UK |
| 5 | Carizmos | Christiano Kerrys | Berguvsesvägen 9 | Luleå | S-968 43 | Sweden |
This is demo example from the "Orders" table in the database:
| ID | Customer_ID | Employee_ID |
|---|---|---|
| 20408 | 2 | 7 |
| 20409 | 2 | 5 |
| 85471 | 1 | 3 |
| 75864 | 5 | 8 |
SQL GROUP BY: Examples
In this example, we list the number of developers in each city:
In this example, we list the number of developers in each city, sorted high to low:
Example
SELECT COUNT(ID), City
FROM Developers
GROUP BY City
ORDER BY COUNT(ID) DESC;In this example, we list the number of orders done by each developer:
Example
SELECT Developers.Name, COUNT(Customer_orders.customer_id) AS OrdersCount
FROM Customer_orders
LEFT JOIN Developers ON Customer_orders.customer_id = Developers.ID
GROUP BY Developers.Name;