Topic 13 of 52
Aggregate Functions
Overview
If standard SQL queries operate on a row-by-row basis, Aggregate Functions collapse multiple rows down into a single mathematical summary. If a CEO asks 'What was our total revenue this year?', you don't send them an Excel sheet with 50,000 individual purchases. You use an Aggregate Function (SUM()) to mathematically crush all 50,000 rows into one single output number.
Syntax
sql
-- The Big 5 Aggregate Functions:
-- 1. COUNT(): How many rows exist?
SELECT COUNT(*) FROM users;
-- 2. SUM(): Add all the numbers together
SELECT SUM(salary) FROM employees;
-- 3. AVG(): Calculate the mathematical mean
SELECT AVG(price) FROM products;
-- 4. MAX(): Find the highest value (Numbers, Dates, or Alphabetical)
SELECT MAX(age) FROM users;
-- 5. MIN(): Find the lowest value
SELECT MIN(created_at) FROM orders;Common Pitfalls
- Mixing aggregated and non-aggregated columns. If you run
SELECT first_name, MAX(salary) FROM employees;, the database will violently crash.MAX()crushes 10,000 salaries into 1 number. Butfirst_namestill has 10,000 names! The database doesn't know which name to print next to the max salary. You must useGROUP BYto fix this. - Assuming
COUNT(column_name)counts everything. It does NOT.COUNT(*)counts the physical rows.COUNT(email)counts only the rows where the email is NOT NULL. If 5 users didn't provide an email, those rows are silently excluded from the count.
Interview Questions
Q:
How do
SUM() and AVG() behave when they encounter a NULL value in the dataset?A:
Standard SQL aggregate functions silently ignore NULL values. If you average salaries, and one person has a NULL salary, they are completely excluded from both the sum AND the denominator count. This can severely skew statistical reports.
Real-World Example
Generating a high-level summary dashboard for an administrator.
example
sql
SELECT
COUNT(*) AS total_registered_users,
SUM(lifetime_value) AS total_revenue_generated,
MAX(last_login) AS most_recent_activity
FROM users
WHERE status = 'active';Check Your Knowledge
Test your understanding of Aggregate Functions with these quick questions.