SQL aggregate functions calculate one value from many rows: COUNT counts rows, SUM adds numbers, AVG averages them, and MIN and MAX find the smallest and largest values. Combined with GROUP BY, they turn thousands of rows into a summary, such as total revenue per region or orders per customer.
Almost every report starts with an aggregate. How many orders did we get? What’s the average order value? Which region sold the most? Aggregate functions answer those questions directly in the database, without exporting data to a spreadsheet.
This guide explains the five core aggregate functions, GROUP BY and HAVING, how NULL values change the results, conditional aggregation, and the differences between databases that cause wrong numbers. Every example was tested on PostgreSQL 17, MySQL 8.4, SQL Server 2022, and SQLite. It’s part of a set with SQL String Functions and SQL Date Functions.
Sample Data for This Guide
The examples use a small orders table. Some orders have a discount and some have NULL (no discount recorded), which matters later.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer VARCHAR(30),
region VARCHAR(10),
product VARCHAR(20),
quantity INT,
unit_price DECIMAL(8,2),
discount DECIMAL(4,2),
order_date DATE
);
INSERT INTO orders VALUES
(1, 'Maria Lopez', 'North', 'Laptop', 1, 900.00, 0.10, '2026-01-05'),
(2, 'James Carter', 'South', 'Monitor', 2, 250.00, NULL, '2026-01-09'),
(3, 'Maria Lopez', 'North', 'Mouse', 3, 25.00, NULL, '2026-01-15'),
(4, 'Sofia Nguyen', 'East', 'Desk', 1, 320.00, 0.05, '2026-01-22'),
(5, 'James Carter', 'South', 'Laptop', 1, 900.00, 0.15, '2026-02-03'),
(6, 'Daniel Brooks', 'West', 'Keyboard', 4, 45.00, NULL, '2026-02-11'),
(7, 'Sofia Nguyen', 'East', 'Monitor', 1, 250.00, NULL, '2026-02-18'),
(8, 'Maria Lopez', 'North', 'Desk', 2, 320.00, 0.10, '2026-02-25'),
(9, 'Daniel Brooks', 'West', 'Laptop', 2, 900.00, 0.05, '2026-03-04'),
(10, 'James Carter', 'South', 'Mouse', 5, 25.00, NULL, '2026-03-12');
The same statements work in all four databases. Each order’s total is quantity * unit_price. The examples show PostgreSQL output; the other databases return the same values unless noted.
Cheat Sheet
| Function | What it returns | Ignores NULLs? | On zero rows |
|---|---|---|---|
COUNT(*) | Number of rows | Counts every row | 0 |
COUNT(column) | Number of non-NULL values | Yes | 0 |
COUNT(DISTINCT column) | Number of different non-NULL values | Yes | 0 |
SUM(column) | Total | Yes | NULL |
AVG(column) | Average | Yes | NULL |
MIN(column) / MAX(column) | Smallest / largest value (numbers, dates, or text) | Yes | NULL |
COUNT: How Many Rows?
COUNT has three forms, and the difference matters:
SELECT COUNT(*) AS all_rows,
COUNT(discount) AS with_discount,
COUNT(DISTINCT customer) AS customers,
COUNT(DISTINCT product) AS products
FROM orders;
all_rows | with_discount | customers | products
----------+---------------+-----------+----------
10 | 5 | 4 | 5
COUNT(*)counts every row: 10 orders.COUNT(discount)counts only rows wherediscountisn’t NULL: 5 orders had a discount.COUNT(DISTINCT customer)counts different values: 10 orders came from 4 customers.
Use COUNT(*) to count rows, and COUNT(column) only when you specifically want to skip NULLs.
SUM, AVG, MIN, and MAX
SELECT SUM(quantity * unit_price) AS revenue,
AVG(quantity * unit_price) AS avg_order,
MIN(quantity * unit_price) AS smallest,
MAX(quantity * unit_price) AS largest
FROM orders;
revenue | avg_order | smallest | largest
---------+----------------------+----------+---------
5690.00 | 569.0000000000000000 | 75.00 | 1800.00
Aggregate functions can work on an expression, like quantity * unit_price, not just a single column. To make the average readable, round it: ROUND(AVG(quantity * unit_price), 2) returns 569.00.
MIN and MAX also work on dates and text:
SELECT MIN(order_date) AS first_order,
MAX(order_date) AS last_order,
MIN(customer) AS first_alphabetically
FROM orders;
first_order | last_order | first_alphabetically
-------------+------------+----------------------
2026-01-05 | 2026-03-12 | Daniel Brooks
Watch Out: AVG of Whole Numbers in SQL Server
SELECT AVG(quantity) AS avg_qty FROM orders;
PostgreSQL, MySQL, and SQLite return 2.2. SQL Server returns 2, because the average of an INT column is also an INT, so the decimal part is cut off. Convert the value first:
SELECT AVG(1.0 * quantity) AS avg_qty FROM orders; -- 2.200000 in SQL Server
How NULL Values Change the Results
Aggregate functions skip NULLs (except COUNT(*)). That’s usually what you want, but not always:
SELECT AVG(discount) AS avg_ignoring_nulls,
AVG(COALESCE(discount, 0)) AS avg_nulls_as_zero
FROM orders;
avg_ignoring_nulls | avg_nulls_as_zero
------------------------+------------------------
0.09000000000000000000 | 0.04500000000000000000
AVG(discount)averages only the 5 discounted orders: 9%.AVG(COALESCE(discount, 0))treats missing discounts as 0 and averages all 10 orders: 4.5%.
Both are correct; they answer different questions. “What’s the typical discount when we give one?” is the first. “What’s the average discount across all orders?” is the second. Decide what NULL means in your data before averaging.
No Matching Rows: SUM Returns NULL, Not 0
SELECT COUNT(*) AS n, SUM(quantity) AS units, MAX(order_date) AS latest
FROM orders
WHERE region = 'Central';
n | units | latest
---+-------+--------
0 | |
COUNT returns 0, but SUM, AVG, MIN, and MAX return NULL (shown as blank) when there are no rows. In reports, wrap them in COALESCE: COALESCE(SUM(quantity), 0) returns 0.
GROUP BY: One Result per Group
Without GROUP BY, an aggregate returns one row for the whole table. With it, you get one row per group:
SELECT region,
COUNT(*) AS orders,
SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY region
ORDER BY revenue DESC;
region | orders | revenue
--------+--------+---------
West | 2 | 1980.00
North | 3 | 1615.00
South | 3 | 1525.00
East | 2 | 570.00
West has the fewest orders but the most revenue, thanks to a two-laptop order. That’s the kind of insight a total alone can’t show.
Group by several columns to get one row per combination:
SELECT region, product, SUM(quantity) AS units
FROM orders
GROUP BY region, product
ORDER BY region, product;
region | product | units
--------+----------+-------
East | Desk | 1
East | Monitor | 1
North | Desk | 2
North | Laptop | 1
North | Mouse | 3
South | Laptop | 1
South | Monitor | 2
South | Mouse | 5
West | Keyboard | 4
West | Laptop | 2
The GROUP BY Rule
Every column in SELECT must either be in GROUP BY or be inside an aggregate function. This query breaks the rule, because a region has several customers and the database can’t know which one to show:
SELECT region, customer FROM orders GROUP BY region;
- PostgreSQL:
ERROR: column "orders.customer" must appear in the GROUP BY clause or be used in an aggregate function - SQL Server:
Column 'orders.customer' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. - MySQL 8:
ERROR 1055 ... incompatible with sql_mode=only_full_group_by - SQLite: runs without error and returns a customer from each region, but which one isn’t defined. Don’t rely on it.
WHERE vs. HAVING
Both filter, but at different times:
WHEREfilters rows before they’re grouped.HAVINGfilters groups after the aggregates are calculated, so it can useSUM,COUNT, and so on.
Customers with more than 1,500 in revenue:
SELECT customer, COUNT(*) AS orders, SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY customer
HAVING SUM(quantity * unit_price) > 1500
ORDER BY revenue DESC;
customer | orders | revenue
---------------+--------+---------
Daniel Brooks | 2 | 1980.00
Maria Lopez | 3 | 1615.00
James Carter | 3 | 1525.00
Using both: customers with at least two orders since February 1:
SELECT customer, SUM(quantity * unit_price) AS revenue
FROM orders
WHERE order_date >= '2026-02-01'
GROUP BY customer
HAVING COUNT(*) >= 2
ORDER BY revenue DESC;
customer | revenue
---------------+---------
Daniel Brooks | 1980.00
James Carter | 1025.00
WHERE removed the January orders first, then HAVING kept only customers with two or more remaining orders. Maria Lopez had only one order after February 1, so she’s gone.
The Order SQL Runs In
SQL is written in one order but evaluated in another, which explains why WHERE can’t use aggregates and HAVING can:
FROM(and joins)WHEREGROUP BY- Aggregate functions
HAVINGSELECTORDER BYLIMIT/TOP
Put conditions on individual rows in WHERE whenever you can; filtering early is faster than filtering groups later.
Conditional Aggregation: Several Totals in One Query
Put a CASE expression inside an aggregate to count or sum only some rows. This builds a small cross-tab in one query:
SELECT region,
SUM(CASE WHEN product = 'Laptop' THEN quantity ELSE 0 END) AS laptops,
SUM(CASE WHEN product <> 'Laptop' THEN quantity ELSE 0 END) AS other_units,
COUNT(CASE WHEN discount IS NOT NULL THEN 1 END) AS discounted_orders
FROM orders
GROUP BY region
ORDER BY region;
region | laptops | other_units | discounted_orders
--------+---------+-------------+-------------------
East | 0 | 2 | 1
North | 1 | 5 | 2
South | 1 | 7 | 1
West | 2 | 4 | 1
COUNT(CASE WHEN ... THEN 1 END) works because CASE returns NULL when the condition is false, and COUNT skips NULLs. This pattern works in every database.
PostgreSQL and SQLite also support a shorter FILTER clause: COUNT(*) FILTER (WHERE discount IS NOT NULL). MySQL and SQL Server don’t, so use CASE there.
Percent of Total
To show each region’s share of revenue, divide each group’s total by the overall total:
SELECT region,
SUM(quantity * unit_price) AS revenue,
ROUND(100.0 * SUM(quantity * unit_price)
/ (SELECT SUM(quantity * unit_price) FROM orders), 1) AS pct
FROM orders
GROUP BY region
ORDER BY revenue DESC;
region | revenue | pct
--------+---------+------
West | 1980.00 | 34.8
North | 1615.00 | 28.4
South | 1525.00 | 26.8
East | 570.00 | 10.0
The subquery calculates the grand total once. You can also use a window function, SUM(SUM(quantity * unit_price)) OVER (), which returns the same result. See SQL Window Functions for how aggregates and window functions differ: an aggregate collapses rows into one per group, while a window function keeps every row. Multiplying by 100.0 instead of 100 avoids integer division.
Subtotals and Grand Totals with ROLLUP
ROLLUP adds a total row to grouped results:
SELECT COALESCE(region, 'Total') AS region, SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY ROLLUP (region)
ORDER BY GROUPING(region), region;
region | revenue
--------+---------
East | 570.00
North | 1615.00
South | 1525.00
West | 1980.00
Total | 5690.00
The total row has NULL in the grouped column, which COALESCE turns into “Total.” GROUPING(region) is 1 on that row, so ordering by it puts the total last. The syntax differs:
| Database | Syntax |
|---|---|
| PostgreSQL, SQL Server | GROUP BY ROLLUP (region) |
| MySQL | GROUP BY region WITH ROLLUP (GROUPING() works too) |
| SQLite | Not supported; use UNION ALL with a separate total query |
Grouping by Month
Group by a date expression to get monthly totals. In PostgreSQL:
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
COUNT(*) AS orders,
SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;
month | orders | revenue
---------+--------+---------
2026-01 | 4 | 1795.00
2026-02 | 4 | 1970.00
2026-03 | 2 | 1925.00
In SQLite use strftime('%Y-%m', order_date); MySQL and SQL Server have their own functions. SQL Date Functions covers grouping by day, week, month, and year in all four databases.
Other Useful Aggregate Functions
| Need | PostgreSQL | MySQL | SQL Server | SQLite |
|---|---|---|---|---|
| Join text from many rows | STRING_AGG | GROUP_CONCAT | STRING_AGG | GROUP_CONCAT |
| Median | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) | (no built-in) | PERCENTILE_CONT as a window function | (no built-in) |
| Standard deviation (sample) | STDDEV_SAMP | STDDEV_SAMP | STDEV | (no built-in) |
The median order value in the sample data is 410, and the average is 569. A few large laptop orders pull the average up, so the median better describes a “typical” order. SQL String Functions shows STRING_AGG and GROUP_CONCAT in action.
Be careful with standard deviation names: in MySQL, plain STDDEV is the population standard deviation, while in PostgreSQL it’s the sample version. Using STDDEV_SAMP or STDDEV_POP makes your intent clear.
Common Mistakes to Avoid
- Using
COUNT(column)when you meantCOUNT(*), and silently skipping rows with NULLs. - Averaging with NULLs without deciding whether a missing value means “unknown” or “zero.”
- Forgetting that
SUMof no rows is NULL, which shows blanks in reports and breaks math. UseCOALESCE(SUM(x), 0). - Integer averages in SQL Server, which drop the decimal part.
- Selecting columns that aren’t grouped or aggregated, which errors in most databases and returns unpredictable values in SQLite.
- Filtering aggregates in
WHERE.WHERE SUM(x) > 100is an error; useHAVING. - Counting duplicates after a join. Joining orders to order items multiplies rows, so
COUNT(*)andSUMget inflated. CountDISTINCTIDs, or aggregate before joining. See SQL JOINs Explained. - Integer division in percentages.
100 * a / bwith whole numbers can round down to 0; use100.0.
Frequently Asked Questions
What are aggregate functions in SQL?
Functions that take many rows and return a single value, such as a count, total, average, minimum, or maximum. The five standard ones are COUNT, SUM, AVG, MIN, and MAX.
What’s the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts all rows. COUNT(column) counts only rows where that column isn’t NULL. COUNT(DISTINCT column) counts the number of different non-NULL values.
Do aggregate functions ignore NULL values?
Yes, all of them except COUNT(*). That means AVG divides only by the number of non-NULL values. Use COALESCE(column, 0) if missing values should count as zero.
What’s the difference between WHERE and HAVING?
WHERE filters individual rows before grouping and can’t use aggregate functions. HAVING filters groups after aggregation and can, for example, HAVING COUNT(*) > 5.
Can I use an aggregate function without GROUP BY?
Yes. Without GROUP BY, the aggregate treats the whole table (after WHERE) as one group and returns one row, such as the total revenue.
What’s the difference between aggregate and window functions?
Aggregate functions with GROUP BY return one row per group. Window functions, written with OVER(), calculate the same kinds of values but keep every original row, which is useful for running totals and rankings.
Conclusion
Aggregate functions are the foundation of SQL reporting. COUNT, SUM, AVG, MIN, and MAX summarize data; GROUP BY splits the summary by category; HAVING filters the groups; and conditional aggregation with CASE builds several totals in one query.
Most wrong numbers come from a few details: NULLs being skipped, SUM returning NULL on empty results, integer averages in SQL Server, and duplicated rows after joins. Check those, test your queries on a small dataset where you know the answers, and your reports will add up.
