sql_group_by

SQL GROUP BY Statement

The GROUP BY statement in SQL is used to arrange identical data into groups. It is almost always used in conjunction with aggregate functions (like COUNT(), SUM(), AVG(), MAX(), MIN()) to calculate metrics for each group.

The GROUP BY Statement Explained

The GROUP BY statement groups rows that have the same values in one or more specified columns into a summary row. For each group, you can apply an aggregate function to calculate a summary statistic.

A crucial rule: When using GROUP BY, any column in the SELECT list that is not part of an aggregate function must be included in the GROUP BY clause. This ensures that each row in the result set corresponds to a unique group.

SQL GROUP BY Syntax

SELECT column_name1, column_name2, ..., aggregate_function(column_nameX)
FROM table_name
WHERE condition
GROUP BY column_name1, column_name2, ...
ORDER BY column_name1, ...;

Logical Order of Operations:

Understanding the logical processing order helps in writing complex queries:

1. FROM (and JOINs): Specifies the tables.
2. WHERE: Filters individual rows before grouping.
3. GROUP BY: Groups rows based on specified columns.
4. HAVING: Filters groups created by GROUP BY (similar to WHERE, but for groups).
5. SELECT: Selects columns and applies aggregate functions.
6. DISTINCT: Removes duplicate rows from the result.
7. ORDER BY: Sorts the final result set.
8. LIMIT / OFFSET / TOP: Restricts the number of rows returned.

Demo Database

In this tutorial, we will use conceptual tables inspired by the Northwind sample database. Imagine we have “Orders”, “Shippers”, and “Employees” tables.

Sample “Orders” table data:

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
10251   | 84         | 3          | 1996-07-08 | 1
10252   | 76         | 4          | 1996-07-09 | 2

Sample “Shippers” table data:

ShipperID | ShipperName      | Phone
--------- | ---------------- | --------------
1         | Speedy Express   | (503) 555-9831
2         | United Package   | (503) 555-3199
3         | Federal Shipping | (503) 555-9931

Sample “Employees” table data:

EmployeeID | LastName  | FirstName
---------- | --------- | ---------
1          | Davolio   | Nancy
2          | Fuller    | Andrew
3          | Leverling | Janet
4          | Peacock   | Margaret
5          | Buchanan  | Steven
6          | Suyama    | Michael

SQL GROUP BY Example: Orders per Shipper

To find the number of orders handled by each shipper, we group by ShipperName and count the OrderIDs:

SELECT S.ShipperName, COUNT(O.OrderID) AS NumberOfOrders
FROM Orders AS O
INNER JOIN Shippers AS S ON O.ShipperID = S.ShipperID
GROUP BY S.ShipperName
ORDER BY NumberOfOrders DESC;

This query joins Orders and Shippers, then groups the results by shipper name, counting the orders for each. The results are then ordered by the count.

GROUP BY More Than One Column: Orders per Shipper and Employee

You can group by multiple columns to get more granular aggregations. For example, to count orders handled by each employee for each shipper:

SELECT S.ShipperName, E.LastName AS EmployeeLastName, COUNT(O.OrderID) AS NumberOfOrders
FROM Orders AS O
INNER JOIN Shippers AS S ON O.ShipperID = S.ShipperID
INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID
GROUP BY S.ShipperName, E.LastName
ORDER BY S.ShipperName, NumberOfOrders DESC;

This creates groups for each unique combination of ShipperName and EmployeeLastName.

When to Use GROUP BY:

• To categorize data and calculate summary statistics for each category (e.g., total sales per region, average order value per customer segment).
• To count occurrences of distinct values in a column.
• To prepare data for charts and reports that show aggregated information.

For filtering these groups based on aggregate function results, you’d use the HAVING clause.