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 return one result per group instead of one result for the whole table. It is how a list of orders becomes revenue per customer, orders per product or sales per month.

This chapter is for beginners who already know SELECT and WHERE and want to start summarising data. Every example runs against the same six-row Orders table, and every result table below shows exactly what the query returns, so you can check your own output line by line.

If you have used pivot tables in Excel or Google Sheets, GROUP BY will feel familiar: the grouping columns are your row labels and the aggregate functions are your values.

SQL GROUP BY Syntax

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_condition
ORDER BY column1;

The golden rule: every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate function. The clause order is always WHERE, then GROUP BY, then HAVING, then ORDER BY (see SQL syntax and clause order).

Sample Table

The six orders total 2,800.

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;

The two Laptop orders (1200 and 1150) are added together into one row.

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;

You can use as many aggregate functions as you like in one grouped query. See COUNT, AVG and SUM and MIN and MAX for how each one treats NULL values.

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;

Example 7: HAVING After GROUP BY

To filter on the aggregate itself, use HAVING. This query keeps only customers with more than one order.

SELECT CustomerID, COUNT(*) AS Orders, SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 1;

More Practical Variations

Count distinct values per group

SELECT SUBSTR(OrderDate, 1, 7) AS OrderMonth, COUNT(DISTINCT Product) AS Products
FROM Orders
GROUP BY SUBSTR(OrderDate, 1, 7)
ORDER BY OrderMonth;

Result: 2026-01 has 3 products (Laptop, Mouse, Monitor), 2026-02 has 2 and 2026-03 has 1.

Conditional counts inside a group

SELECT SUBSTR(OrderDate, 1, 7) AS OrderMonth,
       SUM(CASE WHEN Amount >= 100 THEN 1 ELSE 0 END) AS LargeOrders
FROM Orders
GROUP BY SUBSTR(OrderDate, 1, 7)
ORDER BY OrderMonth;

Result: 2026-01 has 2 large orders (1200 and 300), 2026-02 has 0 and 2026-03 has 1. This pattern lets you build several counts in one pass instead of running separate queries.

Share of total per group

SELECT Product,
       SUM(Amount) AS Revenue,
       ROUND(100.0 * SUM(Amount) / SUM(SUM(Amount)) OVER (), 1) AS PctOfTotal
FROM Orders
GROUP BY Product
ORDER BY Revenue DESC;

Result: Laptop 83.9, Monitor 10.7, Headset 2.9, Keyboard 1.6, Mouse 0.9. The outer SUM(...) OVER () is a window function applied after grouping, so it needs MySQL 8+, SQLite 3.25+, SQL Server or PostgreSQL.

Subtotals with ROLLUP

PostgreSQL and SQL Server support GROUP BY ROLLUP (CustomerID, Product), which adds subtotal rows for each customer and a grand total row (2800). MySQL writes it as GROUP BY CustomerID, Product WITH ROLLUP. SQLite does not support ROLLUP.

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 Errors and How to Fix Them

Tips

• Write the query without GROUP BY first and check the rows, then add the grouping.
• Always alias aggregates (AS Revenue) so the result is readable and sortable.
• Keep the grouping expression identical in SELECT and GROUP BY when grouping by a calculation such as a month.
• Use COUNT(column) instead of COUNT(*) when you want to ignore NULL values.

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

Want more? Test yourself with the SQL quiz with 75 practice questions.

GROUP BY vs. a Pivot Table

A grouped query and a spreadsheet pivot table do the same job: pick row labels, pick a summary function, get one row per label. If you summarise data in spreadsheets as well as SQL, our pivot table guide for Google Sheets shows the spreadsheet side, and the Pivot Tables and Dashboards course on NextGenTemplates Academy takes it further into reporting dashboards.

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

Frequently Asked Questions

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.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before they are grouped. HAVING filters the groups after the aggregates have been calculated, so conditions such as COUNT(*) > 1 belong in HAVING.

Does GROUP BY sort the results?

Not reliably. Some databases happen to return groups in order, but the SQL standard does not guarantee it. Add ORDER BY whenever order matters.

Can I use GROUP BY without an aggregate function?

Yes. It then returns one row per distinct combination, which is the same result as SELECT DISTINCT on those columns.

Wrapping Up

GROUP BY is the step that turns raw rows into answers: totals per customer, counts per product, revenue per month. Remember the rule that every selected column is either grouped or aggregated, filter rows with WHERE and groups with HAVING, and add ORDER BY when you need a sorted result. Next, continue to the SQL HAVING chapter to filter the groups you have just created.

📅 Last updated: October 2026

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