SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
💡 Concept Name
SQL Aggregate Functions perform calculations on multiple rows and return a single summary value.
📘 Quick Intro
Aggregate functions are used to compute values such as totals, averages, minimums, maximums, and counts in SQL queries. They are especially useful with GROUP BY to analyze grouped data.
🧠 Analogy / Short Story
Imagine a class of students. The teacher might count how many students are present (COUNT), calculate the total marks (SUM), the average grade (AVG), the highest score (MAX), and the lowest score (MIN). Aggregate functions are like the teacher’s summary report—they don’t look at individual details but rather the big picture.
🔧 Technical Explanation
- 🔢
COUNT()returns the number of rows. - ➕
SUM()adds all numeric values. - ⚖️
AVG()returns the arithmetic mean. - 📉
MIN()gets the smallest value. - 📈
MAX()gets the largest value. - 🧮 Often used with
GROUP BYto calculate per-group aggregates.
🎯 Purpose & Use Case
- ✅ Count total orders, users, or rows.
- ✅ Calculate average salary, product price, or scores.
- ✅ Find minimum or maximum values in a dataset.
- ✅ Summarize grouped data (e.g., sales per region).
💻 Real Code Example
-- Example Table: Sales
-- Columns: Id, Product, Quantity, Price
-- Total number of sales
SELECT COUNT(*) FROM Sales;
-- Total quantity sold
SELECT SUM(Quantity) FROM Sales;
-- Average price per product
SELECT AVG(Price) FROM Sales;
-- Minimum and maximum price
SELECT MIN(Price), MAX(Price) FROM Sales;
-- Aggregates per product
SELECT Product, SUM(Quantity) AS TotalQty, AVG(Price) AS AvgPrice
FROM Sales
GROUP BY Product;
❓ Interview Q&A
Q1: What does COUNT(*) return?
A: It returns the number of rows in the result set, including NULLs.
Q2: What's the difference between COUNT(*) and COUNT(column)?
A: COUNT(*) counts all rows, while COUNT(column) ignores NULL values in that column.
Q3: Can you use aggregate functions in WHERE clause?
A: No, use HAVING for filtering aggregated data.
Q4: What’s the purpose of GROUP BY with aggregates?
A: It groups rows based on column values and applies aggregate functions on each group.
Q5: What does SUM(Price) do?
A: It adds all the values in the Price column.
Q6: What is AVG used for?
A: It calculates the average value from a column.
Q7: When would you use MAX vs MIN?
A: MAX finds the highest value; MIN finds the lowest.
Q8: Can you use multiple aggregates in one SELECT?
A: Yes, such as SUM and AVG in the same query.
Q9: Are NULL values considered in AVG?
A: No, NULLs are ignored in AVG calculations.
Q10: Can aggregate functions be used in subqueries?
A: Yes, they are commonly used in subqueries for filters or joins.
📝 MCQs
Q1. What does COUNT(*) return?
- Number of columns
- Sum of values
- Number of all rows
- NULL count
Q2. Which function gives the average?
- SUM()
- MAX()
- COUNT()
- AVG()
Q3. Which aggregate returns the highest value?
- MIN()
- SUM()
- MAX()
- TOP()
Q4. What is the result of SUM(Quantity)?
- Average quantity
- Total of all quantity values
- Number of products
- Maximum quantity
Q5. Where do you filter aggregated results?
- WHERE clause
- HAVING clause
- GROUP BY clause
- ORDER BY clause
Q6. What is returned by COUNT(column) when NULLs are present?
- All rows
- NULL only
- Count excluding NULLs
- Zero
Q7. What does AVG ignore?
- Decimals
- Negative values
- NULL values
- Zero
Q8. Which clause groups data for aggregates?
- WHERE
- ORDER BY
- GROUP BY
- HAVING
Q9. Can you use COUNT and AVG together?
- No
- Only in joins
- Yes
- Only in subqueries
Q10. Which function returns the lowest number?
- MAX()
- MIN()
- AVG()
- ROUND()
💡 Bonus Insight
Aggregate functions can be used in subqueries to rank, filter, or join datasets dynamically. When using GROUP BY, always ensure the SELECTed columns are either part of the grouping or wrapped in an aggregate function.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!