SQL GROUP BY Clause: Group Rows and Aggregate Data (Examples)

SQL Tutorial · Chapter 17 of 32

The SQL GROUP BY clause collects rows that share the same value in one or more columns into groups, so aggregate functions such as COUNT, SUM and AVG can produce one result per group instead of one result for the whole table. It turns a list of orders into revenue per customer, orders per product or sales per month.

SQL GROUP BY Syntax

SELECT column1, AGGREGATE(column2)
FROM table_name
WHERE condition
GROUP BY column1
ORDER BY column1;

Every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate function. GROUP BY comes after WHERE and before HAVING and ORDER BY.

Sample Table

Example 1: Count Orders per Customer

SELECT CustomerID, COUNT(*) AS Orders
FROM Orders
GROUP BY CustomerID
ORDER BY CustomerID;

Six rows collapse into five groups, one per distinct CustomerID. Customer 1 has two orders, so their group counts 2.

Example 2: Revenue per Product

SELECT Product, SUM(Amount) AS Revenue
FROM Orders
GROUP BY Product
ORDER BY Revenue DESC;

Example 3: Several Aggregates in One Query

SELECT CustomerID,
       COUNT(*)    AS Orders,
       SUM(Amount) AS Total,
       AVG(Amount) AS Average,
       MAX(Amount) AS Largest
FROM Orders
GROUP BY CustomerID
ORDER BY Total DESC;

Example 4: Group by Several Columns

With two grouping columns you get one row for each distinct combination.

SELECT CustomerID, Product, SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerID, Product
ORDER BY CustomerID, Product;

Example 5: Group by an Expression (Month)

SELECT SUBSTR(OrderDate, 1, 7) AS OrderMonth, COUNT(*) AS Orders, SUM(Amount) AS Revenue
FROM Orders
GROUP BY SUBSTR(OrderDate, 1, 7)
ORDER BY OrderMonth;

SQL Server uses SUBSTRING(OrderDate, 1, 7) or FORMAT(OrderDate, 'yyyy-MM'); MySQL also offers DATE_FORMAT(OrderDate, '%Y-%m').

Example 6: WHERE Before GROUP BY

WHERE removes rows before grouping. Here only orders of 50 or more are counted.

SELECT Product, COUNT(*) AS Orders
FROM Orders
WHERE Amount >= 50
GROUP BY Product;

To filter on the aggregate itself (for example groups with more than one order) use HAVING.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• The core behaviour is identical. SQL Server and PostgreSQL strictly reject any SELECT column that is neither grouped nor aggregated; SQLite and MySQL (with ONLY_FULL_GROUP_BY off) return an arbitrary value instead, which hides bugs.
• MySQL, PostgreSQL and SQLite let you GROUP BY a column alias or position (GROUP BY 1); SQL Server does not.
• NULL values form their own single group in all four.

GROUP BY and Performance

Grouping requires the database to sort or hash the filtered rows on the grouping columns. An index that matches the GROUP BY columns lets the engine read rows already grouped and skip that step. Filter with WHERE before grouping wherever possible: fewer rows into the grouping stage means less memory and a faster query, and the result is identical.

Common Mistakes

• Selecting a column that is not in GROUP BY: SELECT CustomerID, Product, SUM(Amount) ... GROUP BY CustomerID.
• Filtering an aggregate in WHERE (WHERE COUNT(*) > 1); that belongs in HAVING.
• Assuming groups come back sorted; add ORDER BY.
• Using DISTINCT together with GROUP BY on the same columns, which is redundant.

Practice Exercise

1. Show the number of orders and average Amount for each Product.
2. Show total revenue per month for orders above 40.

-- 1
SELECT Product, COUNT(*) AS Orders, AVG(Amount) AS AvgAmount
FROM Orders GROUP BY Product ORDER BY Product;
-- Laptop 2 / 1175, others 1 each

-- 2
SELECT SUBSTR(OrderDate, 1, 7) AS OrderMonth, SUM(Amount) AS Revenue
FROM Orders WHERE Amount > 40
GROUP BY SUBSTR(OrderDate, 1, 7) ORDER BY OrderMonth;
-- 2026-01 1500, 2026-02 125, 2026-03 1150

Related Chapters

• SQL HAVING – filter the groups GROUP BY creates
• SQL COUNT, AVG, SUM – the functions used with GROUP BY
• SQL Window Functions – aggregates without collapsing rows
• SQL from Scratch: full course overview

FAQ

What does GROUP BY do in SQL?

It merges rows with equal values in the grouping columns into one output row, so aggregate functions can be calculated separately for each group.

Can I GROUP BY more than one column?

Yes. List the columns separated by commas. Each distinct combination of values becomes one group.

Why do I get the error “column must appear in the GROUP BY clause”?

Because a column in your SELECT list is neither grouped nor aggregated. Add it to GROUP BY or wrap it in MIN, MAX, SUM, COUNT or AVG.

Chapter 17 of 32 · SQL from Scratch: all 32 chapters