SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX With and Without GROUP BY

Individual rows tell you about one thing at a time; aggregate functions answer questions about many rows at once — how many orders were placed, what's the average rating, which product is the most expensive. Combined with GROUP BY, they become one of the most powerful and frequently used tools in all of SQL for turning raw rows into meaningful summaries.

What Are Aggregate Functions?

Aggregate functions perform a calculation across a set of rows and return a single summarizing value. COUNT, SUM, AVG, MIN, and MAX are the five most common aggregate functions, used either across an entire table (no grouping) or per group when combined with GROUP BY.

What You'll Learn

• Use COUNT, SUM, AVG, MIN, and MAX to summarize an entire table.
• Understand the difference between COUNT(*) and COUNT(column).
• Combine aggregate functions with GROUP BY for per-group summaries.
• Use HAVING to filter groups based on an aggregate result.

Key Terms to Know

• Aggregate function: A function that summarizes multiple rows into a single value.
• COUNT(*): Counts every row, including those with NULL columns.
• COUNT(column): Counts only rows where that specific column is not NULL.
• GROUP BY: A clause that groups rows sharing the same column value(s), so aggregate functions calculate per group.

Aggregate Functions Without GROUP BY

SELECT COUNT(*) FROM orders; returns a single number: the total count of rows in the orders table. SELECT SUM(order_total), AVG(order_total), MIN(order_total), MAX(order_total) FROM orders; returns one row with four summary values: the total revenue, average order value, smallest order, and largest order across the entire table.

Without GROUP BY, every aggregate function collapses the entire table (or whatever WHERE has already filtered it down to) into a single summary row — this is the simplest and most common aggregate use case, answering one overall question about the whole dataset.

COUNT(*) vs COUNT(column), and the Other Aggregates with NULL

COUNT(*) counts every row in the result, regardless of whether any column contains NULL. COUNT(column) counts only rows where that specific column is not NULL, skipping NULL values entirely — this distinction was introduced back in the IS NULL lesson and matters significantly whenever a column has incomplete data.

SUM, AVG, MIN, and MAX all similarly ignore NULL values in their calculations by default — SUM treats NULL rows as if they simply weren't there (not as zero), and AVG's denominator only counts non-NULL rows, which is exactly why AVG can give a different result than SUM(column)/COUNT(*) would suggest.

Combining Aggregates with GROUP BY, and Filtering Groups with HAVING

SELECT category, COUNT(*) AS total_products, AVG(price) AS avg_price FROM products GROUP BY category; calculates COUNT and AVG separately for each distinct category value, returning one summary row per category instead of one row for the whole table. Every column in the SELECT list that isn't inside an aggregate function must appear in the GROUP BY clause, or MySQL (depending on SQL mode) will raise an error or produce unpredictable results.

HAVING filters groups after aggregation has already happened, based on an aggregate result — for example, SELECT category, COUNT(*) AS total FROM products GROUP BY category HAVING COUNT(*) > 10; only returns categories with more than 10 products. This differs fundamentally from WHERE, which filters individual rows before any grouping occurs, and can't reference an aggregate function's result directly.

Visual Summary

Picture a giant pile of individual receipts (rows) from a single day at a store. Without grouping, aggregate functions are like handing the entire pile to an accountant and asking for one final summary: total revenue, average sale, highest sale. GROUP BY is like first sorting that same pile into separate stacks by product category before handing each stack to the accountant separately — now you get one summary per category instead of just one grand summary for everything combined.

The Five Core Aggregate Functions

Function	Returns	NULL Handling
COUNT(*)	Total number of rows	Includes rows with NULL columns
COUNT(column)	Number of non-NULL values in column	Excludes NULL rows in that column
SUM(column)	Total of all values	Ignores NULL rows
AVG(column)	Average of all values	Ignores NULL rows in both sum and count
MIN(column) / MAX(column)	Smallest / largest value	Ignores NULL rows

SQL Example

-- Overall summary across the whole table, no grouping
SELECT COUNT(*) AS total_orders, SUM(order_total) AS total_revenue, AVG(order_total) AS avg_order_value
FROM orders;

-- Per-category summary using GROUP BY
SELECT category, COUNT(*) AS total_products, AVG(price) AS avg_price, MAX(price) AS most_expensive
FROM products
GROUP BY category;

-- Filtering groups with HAVING, after aggregation
SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- Demonstrating COUNT(*) vs COUNT(column) with a nullable column
SELECT COUNT(*) AS total_rows, COUNT(rating) AS rated_products
FROM products;

The first query gives a single overall business summary. The second produces one summary row per product category. The third filters down to only categories with more than 10 products, using HAVING since the filter is based on an aggregate result. The fourth demonstrates how COUNT(*) and COUNT(rating) can diverge whenever the rating column contains any NULL values.

Real-World Examples

• E-commerce dashboards use SUM(order_total) and AVG(order_total) GROUP BY month to track revenue trends over time.
• Inventory reports use COUNT(*) and MIN(stock_qty) GROUP BY category to spot categories running critically low on stock.
• Customer analytics use COUNT(DISTINCT customer_id) GROUP BY country to measure unique active customers per region.
• Quality control systems use AVG(defect_rate) GROUP BY production_line HAVING AVG(defect_rate) > threshold to flag underperforming lines.

Best Practices and Pro Tips

• Always be intentional about COUNT(*) versus COUNT(column) — they answer genuinely different questions whenever the column can contain NULL.
• Use HAVING specifically for conditions involving an aggregate result, and WHERE for conditions on individual row values before grouping — mixing them up is a very common early mistake.
• When combining GROUP BY with multiple non-aggregated columns in the SELECT list, make sure every one of them is also listed in GROUP BY to avoid ambiguous or unpredictable results.

Common Mistakes to Avoid

• Using WHERE instead of HAVING to filter based on an aggregate result like COUNT(*) > 10, which fails since WHERE can't reference aggregate functions.
• Assuming AVG(column) = SUM(column) / COUNT(*) when the column contains NULLs — AVG actually divides by COUNT(column), not COUNT(*), so the two formulas can disagree.
• Selecting a non-aggregated column that isn't included in GROUP BY, leading to ambiguous or inconsistent results depending on MySQL's SQL mode settings.
• Forgetting that aggregate functions ignore NULL values by default, leading to misinterpreted SUM or AVG results on incomplete data.

Interview Questions

Q1. What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row in the result set regardless of NULL values. COUNT(column) counts only rows where that specific column is not NULL, excluding any NULL values in that column from the count.

Q2. What is the difference between WHERE and HAVING when working with aggregate functions?

WHERE filters individual rows before any grouping or aggregation occurs and cannot reference aggregate function results. HAVING filters groups after aggregation has happened, and can reference aggregate results like COUNT(*) or AVG(column) directly.

Q3. Why might AVG(column) give a different result than SUM(column) / COUNT(*) on the same table?

AVG ignores NULL values in both its sum and its denominator, effectively computing SUM(column) / COUNT(column). If the column contains any NULLs, COUNT(column) will be smaller than COUNT(*), causing the two formulas to diverge.

Q4. What rule must be followed when combining non-aggregated columns with GROUP BY?

Every column in the SELECT list that isn't wrapped in an aggregate function must also appear in the GROUP BY clause, or the query can produce ambiguous, unpredictable, or outright rejected results depending on MySQL's SQL mode.

Practice MCQs

1. What does COUNT(*) count that COUNT(rating) might not?

1. Rows where rating is exactly zero
2. Rows where rating is NULL
3. Only the first row
4. Distinct rating values

Answer: B. Rows where rating is NULL

Explanation: COUNT(*) counts every row regardless of NULLs, while COUNT(rating) skips rows where rating is NULL, so COUNT(*) can be larger if any ratings are missing.

2. Which clause should be used to filter groups based on COUNT(*) > 10?

1. WHERE
2. HAVING
3. GROUP BY
4. ORDER BY

Answer: B. HAVING

Explanation: HAVING filters groups after aggregation, and is the only clause that can reference an aggregate function's result like COUNT(*).

3. What does GROUP BY do when combined with an aggregate function?

1. Removes duplicate rows only
2. Calculates the aggregate separately for each distinct group
3. Sorts the result set
4. Filters rows before aggregation

Answer: B. Calculates the aggregate separately for each distinct group

Explanation: GROUP BY partitions rows into groups based on shared column values, and aggregate functions are then calculated independently within each group.

Quick Revision Points

• COUNT(*) counts all rows; COUNT(column) counts only non-NULL values in that column.
• SUM, AVG, MIN, MAX all ignore NULL values by default.
• GROUP BY produces one aggregate result per distinct group; non-aggregated SELECT columns must appear in GROUP BY.
• HAVING filters groups post-aggregation; WHERE filters rows pre-aggregation and cannot use aggregate results.

Conclusion

• Aggregate functions turn raw rows into meaningful summaries, the foundation of virtually all reporting.
• COUNT(*) vs COUNT(column) and WHERE vs HAVING are the two most important distinctions to internalize in this lesson.
• GROUP BY scales aggregate functions from one whole-table summary into many per-group summaries in a single query.

COUNT, SUM, AVG, MIN, and MAX summarize many rows into single meaningful values, whether across an entire table or, combined with GROUP BY, separately per distinct group. Understanding COUNT(*) versus COUNT(column), how NULL is silently excluded from SUM/AVG/MIN/MAX, and the distinct roles of WHERE versus HAVING are essential to using aggregates correctly rather than just syntactically. The next lesson covers IFNULL, NULLIF, and COALESCE — functions specifically designed to handle NULL values gracefully within expressions.