COUNT, SUM, AVG, MIN, MAX

Last Updated: June 12, 2026 • 16 min read

Aggregate functions collapse multiple rows into a single summary value. They are the backbone of reporting, analytics, and data summarization in SQL. Without aggregates, you can only look at individual rows — with them, you can answer questions like "how many?", "what's the total?", "what's the average?", and "what's the range?" across entire datasets or subsets of data.

1. Introduction

The five core aggregate functions — COUNT, SUM, AVG, MIN, MAX — collapse multiple rows into single summary values. COUNT tallies rows, SUM adds numbers, AVG computes means, and MIN/MAX find extremes. Combined with GROUP BY, they transform raw data into per-group statistics. Understanding their NULL handling is essential: COUNT(*) counts all rows, while COUNT(column), SUM, AVG, MIN, and MAX all skip NULLs.

2. Why It Matters

Every dashboard uses aggregates: Revenue totals, user counts, average ratings, and date ranges are all aggregate queries.
NULL handling mistakes: Using COUNT(column) when you mean COUNT(*) produces wrong counts. SUM returns NULL (not 0) for empty groups.
Data quality: COUNT(*) - COUNT(column) reveals NULL rates per column — a key monitoring metric.
Interview cornerstone: COUNT(*) vs COUNT(1), COUNT(column) NULL behavior, and DISTINCT inside aggregates are heavily tested.

3. Real-World Analogy

Aggregate functions are like a class teacher's grade book. COUNT(*) asks "how many students are enrolled?" (counts everyone). COUNT(homework) asks "how many students turned in homework?" (skips NULL entries). AVG(score) computes the class average (ignoring absent students). MIN/MAX find the lowest and highest scores. SUM adds up all points. The teacher never confuses "20 students enrolled" with "18 students submitted" — and neither should your SQL.

4. How It Works

Function	Returns	NULL Handling
COUNT(*)	Total rows	Counts ALL rows
COUNT(col)	Non-NULL values	Skips NULLs
SUM(col)	Total sum	Skips NULLs; NULL if all NULL
AVG(col)	Average	Skips NULLs in both sum and count
MIN/MAX	Smallest/largest	Skips NULLs; works on any orderable type

COUNT(*) vs COUNT(1): No difference — PostgreSQL optimizes both identically. The * doesn't "read all columns."
COUNT(DISTINCT col): Counts unique non-NULL values. Slower than plain COUNT (requires sorting/hashing).
SUM returns NULL for empty input: Use COALESCE(SUM(col), 0) to get 0 instead.
AVG on integers truncates: Cast to AVG(col::numeric) for decimal precision.

5. Internal Architecture

6. Visual Explanation

7. Practical Example

8. Common Mistakes

COUNT(column) vs COUNT(*): If the column has NULLs, COUNT(column) is lower. Use COUNT(*) for "how many rows?" and COUNT(col) for "how many have a value?"
SUM returns NULL, not 0: When all values are NULL or input is empty. Wrap with COALESCE: COALESCE(SUM(col), 0).
AVG on integers truncates: AVG(int_col) returns an integer. Cast: AVG(col::numeric).
Mixing aggregated and non-aggregated columns without GROUP BY: PostgreSQL errors in strict mode.

Interview Insight

"What's the difference between COUNT(*) and COUNT(1)?" — There is none. PostgreSQL optimizes both identically. The * doesn't "read all columns" — it just counts rows.

Common Pitfall

Aggregates without GROUP BY collapse to one row. SELECT department, COUNT(*) FROM employees without GROUP BY is an error. Always pair non-aggregated columns with GROUP BY.

9. Quick Quiz

Q1: A table has 100 rows. 15 rows have NULL in the 'email' column. What does COUNT(email) return?

A) 100 B) 85 C) 15

Answer: B — COUNT(column) skips NULLs, so 100 - 15 = 85.

Q2: What does SUM return when all values in the group are NULL?

A) 0 B) NULL C) Error

Answer: B — SUM returns NULL (not 0) when all values are NULL. Use COALESCE to get 0.

10. Scenario-Based Challenge

Data Quality Audit Report

Your customers table has columns: id, name, email, phone, address, created_at. Write a single query that returns: total rows, NULL percentage for each nullable column, count of unique emails, earliest and latest signup dates, and average account age in days. This single query gives a complete data quality snapshot.

11. Debugging Exercise

This query returns an average of 0 for all departments. Why?

12. Interview Questions

Q: What's the difference between COUNT(*) and COUNT(column)?

A: COUNT(*) counts all rows including NULLs. COUNT(column) counts only rows where that column is non-NULL. The difference (COUNT(*) - COUNT(col)) gives the NULL count for that column.

Q: How do you compute a weighted average in SQL?

A: AVG alone can't compute weighted averages. Use: SUM(value * weight) / SUM(weight). For example, course grades with credit hours: SUM(grade * credits) / SUM(credits).

Q: Can MIN/MAX work on non-numeric types?

A: Yes. MIN/MAX work on any orderable type: strings (alphabetical), dates (chronological), and even arrays (element-by-element comparison). MIN(name) returns the first name alphabetically.

13. Production Considerations

COUNT performance on large tables: SELECT COUNT(*) FROM big_table is slow on PostgreSQL (MVCC — must check row visibility). Use estimates from pg_class.reltuples for approximate counts.
HyperLogLog for DISTINCT: For approximate COUNT(DISTINCT) on billions of rows, use the postgresql-hll extension — O(1) memory vs O(n) for exact counts.
Materialized views: For dashboards querying expensive aggregates, use materialized views with periodic refresh instead of computing on every request.
Overflow protection: PostgreSQL auto-promotes SUM(INTEGER) to BIGINT and SUM(BIGINT) to NUMERIC. In other databases, watch for silent overflow.