This is where SQL turns into business intelligence. COUNT, SUM, AVG, GROUP BY, HAVING — five constructs that power every dashboard at Shopify, Looker, Tableau, and your local SaaS startup. By the end you'll write queries an analyst would ship straight to production.
Learning Objectives
After this lesson, you will be able to:
Use COUNT to count rows that match a condition (and know why `COUNT(*)` and `COUNT(col)` differ)
Use SUM and AVG to calculate totals and averages from numeric columns
Use MIN and MAX to find the smallest and largest values
Group rows by category with GROUP BY to get per-group summaries
Filter grouped results with HAVING (the WHERE for groups)
Combine aggregate functions with WHERE, GROUP BY, ORDER BY, and LIMIT in one query
Explain the difference between WHERE (filters rows) and HAVING (filters groups) — every interview asks this
What Are Aggregate Functions?
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
You are about to unlock the power of summarization. So far, your queries returned individual rows. Now you will learn to answer questions like "how many?" "what is the average?" and "what is the total?" These are the queries that turn raw data into actual insights.
Until now, every query returned individual rows -- one per employee, one per order. Aggregate functions collapse multiple rows into a single summary value.
sql
-- How many employees are there?
SELECT COUNT(*) FROM employees;
This does not return 10 rows. It returns one number: the total count. That is what "aggregate" means -- it combines many values into one.
Try it! Run the query below to count all employees. Then change COUNT(*) to AVG(salary) to see the average salary.
-- Total number of employees
SELECT COUNT(*) FROM employees;
-- Number of employees in Engineering
SELECT COUNT(*) FROM employees
WHERE department = 'Engineering';
-- Count non-NULL values in a specific column
SELECT COUNT(manager_id) FROM employees;
COUNT(*) counts all rows, including those with NULL values.
COUNT(column) counts only rows where that column is NOT NULL.
This difference matters! If 3 out of 10 employees have no manager, COUNT(*) returns 10 but COUNT(manager_id) returns 7.
-- Total salary budget for all employees
SELECT SUM(salary) FROM employees;
-- Total salary for just Engineering
SELECT SUM(salary) FROM employees
WHERE department = 'Engineering';
SUM only works on numeric columns. Trying to SUM a text column gives an error.
-- Average salary across all employees
SELECT AVG(salary) FROM employees;
-- Average salary in Marketing
SELECT AVG(salary) FROM employees
WHERE department = 'Marketing';
AVG ignores NULL values. If 10 employees have salaries but 2 have NULL, AVG divides by 8, not 10.
-- Highest salary
SELECT MAX(salary) FROM employees;
-- Lowest salary
SELECT MIN(salary) FROM employees;
-- Earliest hire date (oldest employee)
SELECT MIN(hire_date) FROM employees;
-- Most recent hire date (newest employee)
SELECT MAX(hire_date) FROM employees;
MIN and MAX work on numbers, dates, and even text (alphabetical order).
You can put several aggregate functions in one query:
sql
SELECT
COUNT(*) AS total_employees,
AVG(salary) AS avg_salary,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary,
SUM(salary) AS total_payroll
FROM employees;
Try it! Run this query to get a complete summary of the employees table.
Loading visualization...
What Do You Think?
What does SELECT COUNT(*) FROM employees WHERE department = 'Sales'; return?
The answer is the number 3 (count of Sales employees). WHERE filters to only Sales department rows, then COUNT(*) counts how many rows remain. The result is a single number, not a list of employees.
Notice the AS keyword in the previous query? It gives your result columns readable names (called aliases):
sql
-- Without AS -- the column is named "AVG(salary)" which is ugly
SELECT AVG(salary) FROM employees;
-- With AS -- the column is named "average_salary" which is clear
SELECT AVG(salary) AS average_salary FROM employees;
Always use AS with aggregate functions. Without it, the column name is the raw function call, which is confusing to read.
sql
SELECT
department,
COUNT(*) AS employee_count,
ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department;
The ROUND function rounds a number. ROUND(AVG(salary), 0) rounds the average to 0 decimal places. You can use ROUND(value, 2) for 2 decimal places.
Every column in SELECT must either be in GROUP BY or inside an aggregate function.
sql
-- CORRECT: department is in GROUP BY, salary is inside AVG()
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- WRONG: name is not in GROUP BY and not in an aggregate
-- SELECT department, name, AVG(salary) FROM employees GROUP BY department;
-- This fails because SQL does not know WHICH name to show for each group!
Think about it: if you group by department, the Engineering group has 4 employees. Which employee's name should SQL display? Alice? Bob? David? Henry? It has no way to decide. That is why you must aggregate or group every column.
Try it! Find the average salary per department. Then modify the query to also show COUNT(*) per department.
-- Average salary per department, but only for employees hired after 2020
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department;
The execution order is:
FROM -- start with all employees
WHERE -- filter to only post-2020 hires
GROUP BY -- split remaining rows into department groups
SELECT -- calculate AVG for each group
Try it! Find the total salary per department, but only for employees earning more than 80000.
WHERE filters individual rows. But what if you want to filter GROUPS? For example, "show me only departments with more than 2 employees." You cannot use WHERE for this because the count does not exist until after grouping.
That is what HAVING does:
sql
-- Departments with more than 2 employees
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;
-- WHERE filters rows, HAVING filters groups
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2019-01-01' -- filter rows first
GROUP BY department
HAVING AVG(salary) > 80000; -- then filter groups
This query says: "For employees hired after 2019, group by department, and show only departments where the average salary is over 80K."
What Do You Think?
What does HAVING COUNT(*) >= 3 do?
The answer is keeps only groups that contain 3 or more rows. HAVING filters groups after GROUP BY. If a department has only 2 employees, it would be excluded by HAVING COUNT(*) >= 3.
Try it! Find departments where the average salary exceeds 80000.
Here is the full order of SQL clauses and when each one runs:
sql
SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees -- 1. Start with all rows
WHERE hire_date > '2019-01-01' -- 2. Filter individual rows
GROUP BY department -- 3. Split into groups
HAVING COUNT(*) >= 2 -- 4. Filter groups
ORDER BY avg_sal DESC -- 5. Sort the results
LIMIT 3; -- 6. Take only top N
Writing order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
Execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
Yes, they are different. SQL does not execute in the order you write it. It starts from FROM, filters with WHERE, groups, filters groups with HAVING, then finally computes the SELECT expressions, sorts, and limits.
Let us practice with the other tables in the database:
sql
-- Total revenue per customer
SELECT customer_name, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_name
ORDER BY total_spent DESC;
-- Number of orders per status
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;
-- Product category summary
SELECT category, COUNT(*) AS num_products, AVG(price) AS avg_price
FROM products
GROUP BY category;
Try it! Find the total revenue per customer, sorted from highest to lowest.
Aggregate functions collapse rows into summaries: COUNT (how many), SUM (total), AVG (mean), MIN (smallest), MAX (largest)
GROUP BY splits data into categories and runs aggregates per group. Every SELECT column must be in GROUP BY or an aggregate
WHERE filters rows before grouping. HAVING filters groups after grouping. They are not interchangeable
AS gives readable names to computed columns. Always alias your aggregates
Execution order differs from writing order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
ROUND cleans up decimals: ROUND(AVG(salary), 2) gives you 2 decimal places
In Snowflake/BigQuery, prefer APPROX_COUNT_DISTINCT for huge cardinality columns — orders of magnitude cheaper than exact COUNT(DISTINCT)
Quick Check1 / 5
What does SELECT COUNT(*) FROM employees return?
What's next: Aggregates summarize one table. But real questions span multiple tables — "average order value per customer," "revenue per product category." That requires JOINs, and that's the next lesson. JOINs are the most important SQL concept you'll ever learn, and we'll spend serious time on the four types and the cardinality traps that take down dashboards.