SQL 29 🛢️ Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
Aggregate functions collapse many rows into a single value. They answer questions like “how many orders were placed,” “what is the total revenue,” “what is the average salary,” “what is the highest price,” and “what is the earliest date.” The five standard aggregates — COUNT, SUM, AVG, MIN, and MAX — are the foundation of reporting and analytics in SQL, and they are almost always used with GROUP BY to produce per-group summaries rather than a single value for the entire table.
The behavior of these functions is not uniform. COUNT behaves differently depending on whether it counts rows or non-NULL values. AVG ignores NULLs, which changes the denominator in ways that surprise people. SUM returns NULL rather than zero when no rows match. These nuances matter because a query that returns the wrong number may still run without error, and the mistake may go unnoticed. Understanding the semantics of each aggregate, and how they interact with NULL and GROUP BY, is essential for writing queries whose results can be trusted.
Key point: Aggregate functions reduce many rows to one value per group. COUNT() counts rows; COUNT(column) counts non-NULL values. All five standard aggregates except COUNT() ignore NULLs. SUM of no rows returns NULL, not zero.
Why aggregate functions exist
The summarization problem. Raw tables contain detail. A table of orders contains one row per order; a table of employees contains one row per employee. Answering “how many,” “how much,” “what is the average,” or “what is the extreme” requires reducing many rows to a single number. Aggregate functions are the mechanism for this reduction, and they are the core of every report, dashboard, and analytical query.
The grouping problem. A single number for the whole table is rarely enough. Managers want totals per department, counts per day, averages per region. GROUP BY partitions rows into groups, and aggregate functions are applied within each group. The combination of GROUP BY and aggregates is what makes SQL a reporting language, not just a storage language.
The NULL problem. NULL represents an unknown or missing value. Aggregates must define how NULL is treated. SQL’s choice is that aggregates (except COUNT(*)) ignore NULLs, and this choice has consequences: AVG computes the average of the values that exist, not the average of all rows. A column with many NULLs will produce an AVG that reflects only the known values, which may or may not be what the analyst intends.
The correctness problem. Aggregates can silently return wrong answers. A SUM that includes duplicate rows from an unintended join, a COUNT that counts NULLs as values, an AVG that divides by the wrong denominator — these are common errors that produce plausible-looking numbers. Understanding the exact semantics of each aggregate, and how joins and NULLs affect them, is the difference between a query that reports correctly and one that misleads.
The performance problem. Aggregates require scanning rows, and on large tables this is expensive. Indexes can sometimes satisfy MIN and MAX without a scan, but COUNT, SUM, and AVG generally require reading the relevant rows. GROUP BY requires sorting or hashing, which adds cost. Understanding how aggregates are executed helps in designing indexes and in deciding when to pre-compute aggregates in summary tables.
a. COUNT: counting rows and non-NULL values
COUNT has two forms with different semantics. COUNT(*) counts all rows in the group, including rows with NULL values in any column. COUNT(column) counts rows where the specified column is not NULL.
-- Count all employees
SELECT COUNT(*) FROM employees;
-- Count employees with a manager assigned
SELECT COUNT(manager_id) FROM employees;If the manager_id column has NULL for employees with no manager, COUNT(*) returns the total number of employees and COUNT(manager_id) returns the number who have a manager. The difference is the number of NULLs.
COUNT(DISTINCT column) counts distinct non-NULL values:
-- Count distinct departments that have at least one employee
SELECT COUNT(DISTINCT department_id) FROM employees;COUNT never returns NULL. If no rows match, COUNT(*) returns 0 and COUNT(column) returns 0. This makes COUNT safe to use in contexts where a NULL result would be problematic.
b. SUM: adding numeric values
SUM adds up the values in a numeric column, ignoring NULLs.
-- Total salary expense
SELECT SUM(salary) FROM employees;
-- Total salary per department
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;The critical behavior: if no rows match the WHERE clause, or if all values are NULL, SUM returns NULL, not zero. This surprises people who expect 0. Coalescing the result to zero requires COALESCE(SUM(column), 0).
-- Returns NULL if no rows match
SELECT SUM(salary) FROM employees WHERE department_id = 99;
-- Returns 0 instead
SELECT COALESCE(SUM(salary), 0) FROM employees WHERE department_id = 99;SUM is only meaningful for numeric columns. Applying it to text or dates is either an error or produces unexpected results, depending on the database.
c. AVG: computing the mean
AVG computes the arithmetic mean of a numeric column, ignoring NULLs.
-- Average salary across all employees
SELECT AVG(salary) FROM employees;
-- Average salary per department
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;The NULL behavior is the source of most AVG errors. Because AVG ignores NULLs, the denominator is the count of non-NULL values, not the count of rows. If a department has 10 employees but 3 have NULL salary, the AVG is computed over 7 values, not 10.
-- These are different when salary has NULLs
SELECT AVG(salary) FROM employees;
SELECT SUM(salary) / COUNT(*) FROM employees;The first divides by the count of non-NULL salaries; the second divides by the count of all rows. If the intent is to average over all rows, treating NULL as zero, the second form is correct. If the intent is to average the known values, the first form is correct. The choice depends on the question being asked.
AVG returns NULL if there are no non-NULL values. It returns a numeric type that may have more decimal places than the input column.
d. MIN and MAX: finding extremes
MIN returns the smallest value in a column; MAX returns the largest. Both ignore NULLs and work with numeric, string, and date columns.
-- Salary range
SELECT MIN(salary) AS lowest, MAX(salary) AS highest FROM employees;
-- Earliest and latest hire dates
SELECT MIN(hire_date) AS first_hire, MAX(hire_date) AS latest_hire
FROM employees;
-- Alphabetical extremes of names
SELECT MIN(last_name) AS first_alpha, MAX(last_name) AS last_alpha
FROM employees;For dates, MIN is the earliest and MAX is the latest. For strings, MIN and MAX use the collation order of the database, which may be case-sensitive or case-insensitive depending on configuration. For numbers, the ordering is numeric.
MIN and MAX return NULL if no rows match or if all values are NULL. Like the other aggregates, they ignore NULL values when computing the extreme among non-NULL values.
A common pattern is to find the row that contains the extreme value, not just the value itself:
-- Employee with the highest salary
SELECT first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);This subquery form works but may return multiple rows if there are ties. A window function (ROW_NUMBER or RANK) provides more control when ties matter.
e. GROUP BY and aggregates
Aggregates become most useful with GROUP BY, which partitions rows into groups and applies the aggregate within each group.
SELECT department_id,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;Every column in the SELECT list that is not an aggregate must appear in the GROUP BY clause. This is a rule in standard SQL, enforced by most databases. The reason is that a non-aggregated column in the SELECT list has no single value per group unless it is part of the grouping key.
-- Error in standard SQL: first_name is not grouped or aggregated
SELECT department_id, first_name, COUNT(*)
FROM employees
GROUP BY department_id;MySQL historically permitted this and returned an arbitrary value for first_name, a behavior that has caused countless bugs. Modern MySQL with ONLY_FULL_GROUP_BY enabled (the default since 5.7) enforces the standard rule.
f. Filtering groups with HAVING
WHERE filters rows before grouping. HAVING filters groups after aggregation. The distinction matters because aggregate results are not available to WHERE.
-- Departments with more than 5 employees
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;WHERE cannot reference an aggregate because WHERE is evaluated before groups exist. HAVING is evaluated after grouping and can reference aggregates. A query can have both:
-- Departments with more than 5 active employees, average salary over 60000
SELECT department_id,
COUNT(*) AS active_count,
AVG(salary) AS avg_salary
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 60000;The WHERE filters inactive employees before grouping; the HAVING filters groups after aggregation.
g. Aggregates and NULL: the complete picture
The NULL behavior of each aggregate is worth summarizing explicitly.
| Aggregate | NULL behavior | Empty result |
|---|---|---|
| COUNT(*) | Counts all rows | 0 |
| COUNT(col) | Counts non-NULL values | 0 |
| COUNT(DISTINCT col) | Counts distinct non-NULL values | 0 |
| SUM | Ignores NULLs | NULL |
| AVG | Ignores NULLs; divides by non-NULL count | NULL |
| MIN | Ignores NULLs | NULL |
| MAX | Ignores NULLs | NULL |
The practical implications: a SUM over a column with NULLs produces a sum of the known values, not an error. An AVG over a column with NULLs computes the mean of the known values. A COUNT(*) over an empty result set returns 0, but a SUM over an empty result set returns NULL. Coalescing SUM and AVG results to a default requires COALESCE.
Complete Example Session
-- ============================================
-- PART 1: CREATE A SAMPLE TABLE
-- ============================================
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(50),
department_id INTEGER,
salary NUMERIC(10, 2),
hire_date DATE,
commission NUMERIC(10, 2)
);
INSERT INTO employees VALUES
(101, 'Alice', 1, 95000, '2020-03-15', NULL),
(102, 'Bob', 1, 78000, '2021-06-01', 5000),
(103, 'Carol', 2, 82000, '2019-11-20', NULL),
(104, 'David', 2, 65000, '2022-01-10', 3000),
(105, 'Eve', 3, 72000, '2020-08-05', NULL),
(106, 'Frank', 3, 68000, '2021-09-15', 2000),
(107, 'Grace', 1, 88000, '2022-04-01', NULL);-- ============================================
-- PART 2: COUNT ROWS AND NON-NULLS
-- ============================================
SELECT COUNT(*) AS total_rows,
COUNT(commission) AS with_commission,
COUNT(*) - COUNT(commission) AS without_commission
FROM employees;-- ============================================
-- PART 3: SUM WITH COALESCE
-- ============================================
-- SUM returns NULL if no rows match.
SELECT SUM(salary) AS total_salary FROM employees;
SELECT COALESCE(SUM(salary), 0) AS total_salary
FROM employees
WHERE department_id = 99;-- ============================================
-- PART 4: AVG AND THE NULL DENOMINATOR
-- ============================================
-- AVG ignores NULLs; compare the two forms.
SELECT AVG(commission) AS avg_of_non_null,
SUM(commission) / COUNT(*) AS avg_over_all_rows
FROM employees;-- ============================================
-- PART 5: MIN AND MAX
-- ============================================
-- Works on numbers, dates, and strings.
SELECT MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary,
MIN(hire_date) AS first_hire,
MAX(hire_date) AS latest_hire
FROM employees;-- ============================================
-- PART 6: GROUP BY WITH ALL AGGREGATES
-- ============================================
SELECT department_id,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;-- ============================================
-- PART 7: HAVING FILTERS GROUPS
-- ============================================
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 75000;-- ============================================
-- PART 8: WHERE FILTERS ROWS BEFORE GROUPING
-- ============================================
SELECT department_id, COUNT(*) AS high_earners
FROM employees
WHERE salary > 70000
GROUP BY department_id
HAVING COUNT(*) >= 2;-- ============================================
-- PART 9: COUNT DISTINCT
-- ============================================
SELECT COUNT(DISTINCT department_id) AS distinct_departments
FROM employees;-- ============================================
-- PART 10: ROW WITH THE MAXIMUM VALUE
-- ============================================
-- Find the employee with the highest salary.
SELECT first_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);These ten parts cover the five standard aggregates, their NULL behavior, the distinction between COUNT(*) and COUNT(column), the AVG denominator issue, GROUP BY with multiple aggregates, WHERE versus HAVING, COUNT DISTINCT, and finding the row containing the extreme value. Each pattern addresses a different analytical question.
Quick Reference
The Five Standard Aggregates
| Function | Returns | NULL Behavior | Empty Result |
|---|---|---|---|
| COUNT(*) | Number of rows | Counts all rows | 0 |
| COUNT(col) | Non-NULL count | Ignores NULLs | 0 |
| SUM | Total | Ignores NULLs | NULL |
| AVG | Mean | Ignores NULLs | NULL |
| MIN | Smallest | Ignores NULLs | NULL |
| MAX | Largest | Ignores NULLs | NULL |
COUNT Variants
| Form | Counts |
|---|---|
| COUNT(*) | All rows including NULLs |
| COUNT(col) | Non-NULL values |
| COUNT(DISTINCT col) | Distinct non-NULL values |
WHERE vs HAVING
| Clause | Filters | Aggregate Available |
|---|---|---|
| WHERE | Rows before grouping | No |
| HAVING | Groups after aggregation | Yes |
Aggregate Use Cases
| Question | Aggregate |
|---|---|
| How many rows? | COUNT(*) |
| How many have a value? | COUNT(col) |
| How many distinct values? | COUNT(DISTINCT col) |
| What is the total? | SUM |
| What is the average? | AVG |
| What is the smallest/largest? | MIN / MAX |
Best Practices
✅ Do This:
SELECT COUNT(*) FROM employees; -- Count rows
SELECT COUNT(commission) FROM employees; -- Count non-NULLs
SELECT COALESCE(SUM(salary), 0) FROM employees; -- Default for empty
SELECT AVG(salary) FROM employees; -- Mean of non-NULLs
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
SELECT department_id, AVG(salary) FROM employees
GROUP BY department_id HAVING AVG(salary) > 70000;❌ Don’t Do This:
SELECT COUNT(commission) FROM employees; -- ❌ If you meant row count
SELECT SUM(salary) FROM employees WHERE false; -- ❌ Returns NULL, not 0
SELECT department_id, first_name, COUNT(*) FROM employees
GROUP BY department_id; -- ❌ first_name not grouped
SELECT AVG(commission) FROM employees; -- ❌ Divides by non-NULL count onlyCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| COUNT returns unexpected number | COUNT(col) skips NULLs | Use COUNT(*) for row count |
| SUM returns NULL | No rows matched | Wrap in COALESCE |
| AVG denominator wrong | NULLs excluded | Use SUM/COUNT(*) for all-row average |
| Non-grouped column error | Column not in GROUP BY | Add to GROUP BY or aggregate |
| HAVING used for row filter | Aggregate not needed | Use WHERE for row-level conditions |
| JOIN inflates aggregate | Duplicate rows from join | Use DISTINCT or pre-aggregate |
| MIN/MAX string ordering surprise | Collation affects ordering | Check collation settings |
| AVG precision unexpected | Integer division or type | Cast to decimal if needed |
Real-World Examples
1. Total Revenue
SELECT SUM(price * quantity) AS total_revenue FROM order_items;2. Employee Count per Department
SELECT department_id, COUNT(*) AS headcount
FROM employees GROUP BY department_id;3. Average Order Value
SELECT AVG(total) AS avg_order_value FROM orders;4. Highest and Lowest Price
SELECT MIN(price) AS cheapest, MAX(price) AS priciest FROM products;5. First and Last Order Date
SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders;6. Departments with High Average Salary
SELECT department_id, AVG(salary) AS avg_salary
FROM employees GROUP BY department_id HAVING AVG(salary) > 80000;7. Count of Distinct Customers
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;8. Employee with Highest Salary
SELECT first_name, last_name, salary
FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);9. Daily Order Count
SELECT DATE(order_date) AS day, COUNT(*) AS orders
FROM orders GROUP BY DATE(order_date) ORDER BY day;10. Conditional Aggregate
SELECT COUNT(*) AS total,
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count
FROM users;Visual
Aggregate Function Behavior
┌──────────────────────────────────────────────────────────────┐
│ HOW AGGREGATES REDUCE ROWS │
│ │
│ Input rows (salary column): │
│ ┌─────────┐ │
│ │ 95000 │ │
│ │ 78000 │ │
│ │ 82000 │ │
│ │ 65000 │ │
│ │ 72000 │ │
│ └─────────┘ │
│ │ │
│ ├── COUNT(*) → 5 │
│ ├── SUM(salary) → 392000 │
│ ├── AVG(salary) → 78400 │
│ ├── MIN(salary) → 65000 │
│ └── MAX(salary) → 95000 │
│ │
│ Many rows in, one value out per aggregate. │
└──────────────────────────────────────────────────────────────┘COUNT(*) vs COUNT(column)
┌──────────────────────────────────────────────────────────────┐
│ COUNT(*) COUNTS ROWS; COUNT(col) SKIPS NULLs │
│ │
│ ┌────┬────────┬────────────┐ │
│ │ id │ name │ commission │ │
│ ├────┼────────┼────────────┤ │
│ │101 │ Alice │ NULL │ │
│ │102 │ Bob │ 5000 │ │
│ │103 │ Carol │ NULL │ │
│ │104 │ David │ 3000 │ │
│ └────┴────────┴────────────┘ │
│ │
│ COUNT(*) → 4 (all rows) │
│ COUNT(commission) → 2 (non-NULL only) │
│ COUNT(DISTINCT commission) → 2 (5000 and 3000) │
│ │
│ The difference between COUNT(*) and COUNT(col) is the │
│ number of NULL values in that column. │
└──────────────────────────────────────────────────────────────┘AVG and the NULL Denominator
┌──────────────────────────────────────────────────────────────┐
│ AVG IGNORES NULLs: DIFFERENT ANSWERS │
│ │
│ commission values: NULL, 5000, NULL, 3000 │
│ │
│ AVG(commission): │
│ └── (5000 + 3000) / 2 = 4000 │
│ Divides by non-NULL count │
│ │
│ SUM(commission) / COUNT(*): │
│ └── (5000 + 3000) / 4 = 2000 │
│ Divides by all rows │
│ │
│ Neither is wrong; the question determines the right form. │
│ "Average commission among those who earn it" → 4000 │
│ "Average commission across all employees" → 2000 │
└──────────────────────────────────────────────────────────────┘WHERE vs HAVING
┌──────────────────────────────────────────────────────────────┐
│ WHERE FILTERS ROWS; HAVING FILTERS GROUPS │
│ │
│ All rows │
│ │ │
│ ▼ │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ WHERE salary > 50000 │ │
│ │ (filters individual rows before grouping) │ │
│ └────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ GROUP BY department_id │ │
│ └────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ HAVING COUNT(*) > 5 │ │
│ │ (filters groups after aggregation) │ │
│ └────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ Final result │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Five standard aggregates | COUNT, SUM, AVG, MIN, MAX |
| COUNT(*) | Counts all rows including NULLs |
| COUNT(col) | Counts non-NULL values |
| COUNT(DISTINCT col) | Counts distinct non-NULL values |
| SUM empty result | NULL (use COALESCE for 0) |
| AVG denominator | Non-NULL count only |
| MIN/MAX | Work on numbers, dates, strings |
| GROUP BY | Partitions rows for per-group aggregation |
| WHERE | Filters rows before grouping |
| HAVING | Filters groups after aggregation |
| Non-aggregated columns | Must appear in GROUP BY |
Key takeaways:
- COUNT(*) and COUNT(column) differ. COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column. The difference is the number of NULLs.
- All aggregates except COUNT(*) ignore NULLs. SUM, AVG, MIN, and MAX operate on the non-NULL values only. This affects the denominator in AVG and the result when all values are NULL.
- SUM and AVG return NULL for no rows. COUNT returns 0. Use COALESCE to default SUM and AVG to zero when an empty result should display as 0.
- AVG divides by the non-NULL count. This is the average of the known values. For the average across all rows, use SUM(column) / COUNT(*).
- GROUP BY partitions rows. Non-aggregated columns in the SELECT list must appear in the GROUP BY clause. Most databases enforce this rule.
- WHERE filters rows before grouping; HAVING filters groups. Use WHERE for row-level conditions and HAVING for aggregate conditions.
- COUNT(DISTINCT) counts unique values. It is useful for answering “how many different customers placed orders” rather than “how many orders.”
- Aggregates can be inflated by joins. A join that produces duplicate rows will inflate SUM and COUNT. Verify the join or pre-aggregate before joining.
Remember: Aggregate functions are the mechanism for summarizing data in SQL. COUNT, SUM, AVG, MIN, and MAX reduce many rows to one value per group, and they are almost always used with GROUP BY for reporting. The semantics that matter most are NULL handling and the denominator in AVG. COUNT(*) counts rows; COUNT(column) counts non-NULL values; all other aggregates ignore NULLs. SUM and AVG return NULL when no rows match, not zero. AVG divides by the count of non-NULL values, which is the average of the known values, not the average across all rows. WHERE filters rows before grouping; HAVING filters groups after aggregation. Understanding these rules prevents the most common errors in analytical queries and produces results that can be trusted.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!