Once you can read rows from a table, the next question is almost always a summary. How many orders did we get? What’s the total per country? Which product sells best? Nobody wants to count those by hand from a list of thousands of rows.
That’s the job of GROUP BY and the aggregate functions: COUNT, SUM, AVG, MIN and MAX. Together they turn a long list into a short report. I use them every day for accounting and sales reports, and they’re much easier than they sound.
Quick answer:
Aggregate functions turn many rows into one value: COUNT counts, SUM adds up, AVG averages, MIN and MAX find the smallest and largest.GROUP BY country runs them once per country instead of once for the whole table.HAVING filters the groups afterwards, for example HAVING COUNT(*) > 1.

Table of Contents
The Table We’ll Use
Every example uses this small orders table from an online shop that sells SQL learning material:
| id | customer | country | product | amount |
|---|---|---|---|---|
| 1 | Sara | Egypt | SQL Handbook | 19.00 |
| 2 | John | UK | SQL Handbook | 19.00 |
| 3 | Sara | Egypt | DB Cheat Sheet | 9.00 |
| 4 | Mei | China | SQL Course | 49.00 |
| 5 | Carlos | Mexico | SQL Handbook | 19.00 |
| 6 | John | UK | SQL Course | 49.00 |
| 7 | Aisha | Egypt | DB Cheat Sheet | 9.00 |
| 8 | Mei | China | SQL Handbook | 19.00 |
Set it up yourself
Type the examples as you read, and they’ll stick far better. Run this once in SQL Server, MySQL or PostgreSQL:
CREATE TABLE orders (
id INT PRIMARY KEY,
customer VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL,
product VARCHAR(50) NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO orders (id, customer, country, product, amount) VALUES
(1, 'Sara', 'Egypt', 'SQL Handbook', 19),
(2, 'John', 'UK', 'SQL Handbook', 19),
(3, 'Sara', 'Egypt', 'DB Cheat Sheet', 9),
(4, 'Mei', 'China', 'SQL Course', 49),
(5, 'Carlos', 'Mexico', 'SQL Handbook', 19),
(6, 'John', 'UK', 'SQL Course', 49),
(7, 'Aisha', 'Egypt', 'DB Cheat Sheet', 9),
(8, 'Mei', 'China', 'SQL Handbook', 19);
New to SQL altogether? Start with SQL for Complete Beginners, then come back here.
Aggregate Functions: Many Rows In, One Value Out
An aggregate function looks at a whole column and gives back a single answer. Here are all five at once, on the full table:
SELECT COUNT(*) AS orders,
SUM(amount) AS total,
AVG(amount) AS average,
MIN(amount) AS cheapest,
MAX(amount) AS biggest
FROM orders;
| orders | total | average | cheapest | biggest |
|---|---|---|---|---|
| 8 | 192.00 | 24.00 | 9.00 | 49.00 |
Eight rows went in, one row came out. That’s what “aggregate” means: it gathers many values into one.
COUNT: How Many?
COUNT comes in three versions, and the difference matters:
| You write | It counts | Here |
|---|---|---|
COUNT(*) | Every row | 8 |
COUNT(amount) | Rows where amount is not NULL | 8 |
COUNT(DISTINCT customer) | Different values, each once | 5 |
SELECT COUNT(*) AS orders,
COUNT(DISTINCT customer) AS customers
FROM orders;
-- 8 orders, from 5 different customers
Our table has no empty amounts, so the first two give the same answer. In real data they often don’t, and COUNT(column) quietly skips the empty ones. NULL in SQL Explained Simply shows why.
GROUP BY: One Row per Group
Totals for the whole table are useful, but the real questions are usually “per something”: per country, per customer, per month. That’s what GROUP BY does.
Picture the database sorting the rows into piles, one pile for each country. Then it runs your aggregate functions on each pile separately and gives you one row per pile:
SELECT country,
COUNT(*) AS total_orders,
SUM(amount) AS total_revenue
FROM orders
GROUP BY country
ORDER BY total_revenue DESC, country;
| country | total_orders | total_revenue |
|---|---|---|
| China | 2 | 68.00 |
| UK | 2 | 68.00 |
| Egypt | 3 | 37.00 |
| Mexico | 1 | 19.00 |
Egypt has the most orders, but China and the UK earned more, because they bought the SQL Course. That’s exactly the kind of thing you only see once the data is grouped.
The one rule you must remember
Every column in your SELECT must either be in the GROUP BY, or be inside an aggregate function. This query breaks the rule:
SELECT country, customer
FROM orders
GROUP BY country;
-- Error: column "customer" must appear in the GROUP BY clause
-- or be used in an aggregate function
It makes sense once you think about the piles. The Egypt pile has three orders from two customers. Which customer should the database show on Egypt’s single row? It can’t know, so it refuses. Either group by customer too, or use something like COUNT(DISTINCT customer) to summarise it.
SUM, AVG, MIN and MAX per Group
You can use as many aggregate functions as you like in one query. This gives a small sales report by country:
SELECT country,
COUNT(*) AS total_orders,
SUM(amount) AS total_revenue,
ROUND(AVG(amount), 2) AS avg_order,
MIN(amount) AS smallest_order,
MAX(amount) AS biggest_order
FROM orders
GROUP BY country
ORDER BY total_revenue DESC, country;
| country | total_orders | total_revenue | avg_order | smallest_order | biggest_order |
|---|---|---|---|---|---|
| China | 2 | 68.00 | 34.00 | 19.00 | 49.00 |
| UK | 2 | 68.00 | 34.00 | 19.00 | 49.00 |
| Egypt | 3 | 37.00 | 12.33 | 9.00 | 19.00 |
| Mexico | 1 | 19.00 | 19.00 | 19.00 | 19.00 |
ROUND(..., 2) keeps the average to two decimal places, which is what you want for money.
A trap for SQL Server users: if the column is a whole-number type like INT, SQL Server’s AVG also returns a whole number, so Egypt’s average would show as 12 instead of 12.33. That’s why the setup script uses DECIMAL(10,2). If you’re stuck with an INT column, write AVG(amount * 1.0).
Grouping by More Than One Column
Add a second column to GROUP BY and you get one row for each combination:
SELECT country, product, COUNT(*) AS times_sold
FROM orders
GROUP BY country, product
ORDER BY country, product;
| country | product | times_sold |
|---|---|---|
| China | SQL Course | 1 |
| China | SQL Handbook | 1 |
| Egypt | DB Cheat Sheet | 2 |
| Egypt | SQL Handbook | 1 |
| Mexico | SQL Handbook | 1 |
| UK | SQL Course | 1 |
| UK | SQL Handbook | 1 |
Now you can see that the cheat sheet only sells in Egypt. With a bigger table, this one query could guide a whole marketing plan.
HAVING: Filter the Groups
WHERE filters rows before they’re grouped. But sometimes you want to filter the groups themselves, like “only countries with more than one order”. You can’t do that with WHERE, because the count doesn’t exist yet when WHERE runs. That’s what HAVING is for:
SELECT country, COUNT(*) AS total_orders
FROM orders
GROUP BY country
HAVING COUNT(*) > 1
ORDER BY country;
-- China 2, Egypt 3, UK 2 (Mexico is gone: it has only 1 order)
You can use both in one query. Here we ignore the cheat sheet, then keep only countries whose remaining sales are over 20:
SELECT country, SUM(amount) AS total_revenue
FROM orders
WHERE product <> 'DB Cheat Sheet'
GROUP BY country
HAVING SUM(amount) > 20
ORDER BY total_revenue DESC, country;
-- China 68.00, UK 68.00
Step by step: WHERE removes the three cheat sheet rows, GROUP BY makes one pile per country, HAVING drops Egypt (19) and Mexico (19), and ORDER BY sorts what’s left. HAVING vs WHERE in SQL goes deeper into the difference.
The Order You Write the Clauses
SQL is strict about the order of clauses. You don’t need all of them every time, but the ones you use must come in this order:
SELECT -- which columns and summaries to show
FROM -- which table
WHERE -- filter rows (before grouping)
GROUP BY -- make the groups
HAVING -- filter groups (after grouping)
ORDER BY -- sort the result
LIMIT -- keep the first N rows (SQL Server: TOP)
Practice: Five Questions on a Sales Table
Here’s a new table to practise on. Create it, then write each query yourself before you look at the answers.
CREATE TABLE sales (
id INT PRIMARY KEY,
salesperson VARCHAR(50) NOT NULL,
region VARCHAR(50) NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO sales (id, salesperson, region, amount) VALUES
(1, 'Ahmed', 'North', 500),
(2, 'Sara', 'South', 300),
(3, 'Ahmed', 'North', 700),
(4, 'Mei', 'East', 450),
(5, 'Sara', 'South', 600),
(6, 'Mei', 'East', 800),
(7, 'Ahmed', 'North', 200),
(8, 'Sara', 'South', 400);
- What is the total sales amount for each salesperson?
- What is the average sale in each region?
- Which salesperson has the highest total?
- Which regions sold more than 1,250 in total?
- How many sales of 500 or more did each salesperson make?
Answers
-- 1. Ahmed 1400.00, Mei 1250.00, Sara 1300.00
SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson
ORDER BY salesperson;
-- 2. East 625.00, North 466.67, South 433.33
SELECT region, ROUND(AVG(amount), 2) AS avg_sale
FROM sales
GROUP BY region
ORDER BY region;
-- 3. Ahmed 1400.00
SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson
ORDER BY total_sales DESC
LIMIT 1; -- SQL Server: SELECT TOP (1) ...
-- 4. North 1400.00, South 1300.00
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 1250;
-- 5. Ahmed 2, Mei 1, Sara 1
SELECT salesperson, COUNT(*) AS number_of_sales
FROM sales
WHERE amount >= 500
GROUP BY salesperson
ORDER BY salesperson;
Question 5 is the one to think about. The “500 or more” rule is about single sales, so it goes in WHERE. Question 4’s “more than 1,250” is about a total, so it goes in HAVING.
Common Mistakes
- Selecting a column that isn’t grouped. Every column must be in
GROUP BYor inside an aggregate. (Older MySQL settings let it through and show any value from the group, which is worse than an error.) - Putting an aggregate in WHERE.
WHERE SUM(amount) > 20fails. Totals belong inHAVING. - Forgetting that COUNT(column) skips NULLs. Use
COUNT(*)when you mean “how many rows”. - Whole-number averages in SQL Server.
AVGof anINTcolumn drops the decimals. - Expecting grouped results to come out sorted.
GROUP BYdoesn’t promise any order. AddORDER BY.
Quick Recap
| Part | What it does | Example |
|---|---|---|
COUNT(*) | Counts rows | COUNT(*) |
SUM() | Adds up values | SUM(amount) |
AVG() | Averages values | ROUND(AVG(amount), 2) |
MIN() / MAX() | Smallest / largest value | MAX(amount) |
GROUP BY | One result row per group | GROUP BY country |
HAVING | Filters the groups | HAVING COUNT(*) > 1 |
Frequently Asked Questions
What is the difference between GROUP BY and DISTINCT?
SELECT DISTINCT country and SELECT country ... GROUP BY country return the same list. The difference is that GROUP BY lets you calculate something for each group, like a count or a total. If you only need the unique values, DISTINCT is simpler. See SQL DISTINCT and Column Aliases.
Can I group by a column that is not in the SELECT list?
Yes. SELECT COUNT(*) FROM orders GROUP BY country is valid. You get one count per country, but without the country name next to it, which is rarely useful. Usually you’ll want to show the grouped column too.
Is COUNT(1) faster than COUNT(*)?
No. Modern databases treat them exactly the same. Use COUNT(*): it’s the standard way to say “count the rows”.
Can I group by a calculation, like the month of a date?
Yes, and it’s very common in reports. For example, in SQL Server, GROUP BY YEAR(order_date), MONTH(order_date) gives one row per month. SQL Date Functions Explained Simply shows the patterns for each database.
What to Learn Next
You can now turn any table into a summary. The natural next steps are:
- SQL JOINs: group data that lives in more than one table, like orders with customer details.
- SQL Window Functions: totals and rankings without collapsing the rows.
- Database Normalization: how to design the tables you’re summarising.
Related articles
- HAVING vs WHERE in SQL: filtering rows versus filtering groups.
- The SQL CASE Expression: if/then logic inside a query.
- Basic Statistics in SQL: average, median, percentiles and standard deviation, step by step.
- How to Learn SQL: A Step-by-Step Roadmap: what to learn next, in order.