SQL GROUP BY Clause

The GROUP BY clause in SQL groups rows with the same values in specified columns into summary rows. This is incredibly useful for summarizing and analyzing data.

GROUP BY: Definition and Usage

GROUP BY is typically used with aggregate functions (like COUNT(), SUM(), AVG(), MAX(), MIN()) to perform calculations on grouped data. It groups rows that have the same values in the specified columns and then the aggregate function can calculate values for each group. This allows you to get totals, averages, or other summary statistics for different categories of data within your tables.

Syntax


SELECT column_name(s), aggregate_function(column_name), ...
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example Databases

The examples below use data from the 'Customers', 'Orders', and 'Shippers' tables.

Customers Table

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

Orders Table

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

Shippers Table

ShipperID	ShipperName
1	Speedy Express
2	United Package
3	Federal Shipping

Examples

Counting Customers per Country

This query counts the number of customers in each country.

Syntax


SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Output


COUNT(CustomerID) | Country
-------------------------
1                 | Germany
2                 | Mexico
1                 | UK
1                 | Sweden

Grouping and Sorting

This query groups customers by country and sorts the results to show countries with the most customers first.

Syntax


SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

Output


COUNT(CustomerID) | Country
-------------------------
2                 | Mexico
1                 | Germany
1                 | UK
1                 | Sweden

GROUP BY with JOIN

This example uses GROUP BY with a LEFT JOIN to count orders per shipper.

Syntax


SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

Output


ShipperName       | NumberOfOrders
---------------------------------
Speedy Express     | 1
United Package    | 1
Federal Shipping  | 1