What are Aggregate Functions in SQL?
Learn SQL aggregate functions COUNT, SUM, AVG, MIN and MAX, how they handle NULLs, work with GROUP BY, plus examples and common interview questions.
Expected Interview Answer
Aggregate functions take a set of rows and return a single summary value; the core ones are COUNT, SUM, AVG, MIN, and MAX.
They operate over all rows of a table or, when combined with GROUP BY, over each group separately. Most aggregates ignore NULL values — for example AVG divides by the count of non-NULL entries — while COUNT(*) counts every row including those with NULLs. Aggregates are commonly paired with GROUP BY for per-category summaries and HAVING to filter those summaries.
- Turn many rows into a single meaningful metric
- Power totals, averages, counts and extremes
- Combine with GROUP BY for per-category summaries
- Underpin reporting and analytics queries
- Handle NULLs predictably for accurate stats
AI Mentor Explanation
An aggregate function is like the scorer announcing a batter's final figures. Individual deliveries are many, but SUM gives the total runs, AVG gives the batting average, and MAX gives the highest score. Countless balls reduce to a handful of headline numbers everyone quotes.
Step-by-Step Explanation
Step 1
Pick the function
Choose COUNT, SUM, AVG, MIN or MAX based on the metric you need.
Step 2
Decide the scope
Without GROUP BY it summarizes the whole result set; with GROUP BY it summarizes per group.
Step 3
Handle NULLs
SUM, AVG, MIN and MAX ignore NULLs; COUNT(*) counts all rows, COUNT(col) skips NULLs.
Step 4
Filter groups if needed
Use HAVING to keep only groups whose aggregate meets a condition.
Step 5
Alias and order
Give aggregates readable aliases and sort with ORDER BY for clean output.
What Interviewer Expects
- Naming the five core aggregate functions
- How aggregates interact with GROUP BY
- NULL handling, especially COUNT(*) vs COUNT(col)
- Difference between COUNT(*) and COUNT(DISTINCT col)
- A correct example query
Common Mistakes
- Thinking AVG counts NULLs in its divisor
- Confusing COUNT(*) with COUNT(column)
- Forgetting DISTINCT when unique counts are needed
- Using aggregates in WHERE instead of HAVING
- Mixing aggregated and non-aggregated columns without GROUP BY
Best Answer (HR Friendly)
“Aggregate functions take a whole column of values and boil them down to one number, like a total, an average, or the highest value. They are how SQL answers questions such as how many customers we have or what our total sales were.”
Code Example
SELECT COUNT(*) AS total_orders,
COUNT(DISTINCT customer_id) AS unique_customers,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders;Follow-up Questions
- What is the difference between COUNT(*) and COUNT(column)?
- How do aggregate functions treat NULL values?
- When would you use COUNT(DISTINCT column)?
- How do aggregates behave with GROUP BY?
- Can you nest aggregate functions, and how do window functions differ?
MCQ Practice
1. Which aggregate function returns the number of rows?
COUNT returns how many rows match; COUNT(*) counts every row including those with NULLs.
2. How does AVG treat NULL values?
AVG sums non-NULL values and divides by the count of non-NULL entries, ignoring NULLs completely.
3. Which counts only unique non-NULL values in a column?
COUNT(DISTINCT column) removes duplicates and NULLs, counting only distinct real values.
Flash Cards
Name the core aggregate functions. — COUNT, SUM, AVG, MIN and MAX.
COUNT(*) vs COUNT(col)? — COUNT(*) counts all rows; COUNT(col) counts rows where col is not NULL.
Do aggregates count NULLs? — SUM, AVG, MIN, MAX ignore NULLs; only COUNT(*) includes NULL rows.
How to count unique values? — Use COUNT(DISTINCT column) to count distinct non-NULL entries.