Aggregate Functions & GROUP BY

#What Are Aggregate Functions?

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.

-- 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.

#The Five Core Aggregate Functions

#COUNT — How Many?

-- 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;

#SUM — What Is the Total?

-- 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.

#AVG — What Is the Average?

-- 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.

#MIN and MAX — What Are the Extremes?

-- 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).

#Using Multiple Aggregates Together

You can put several aggregate functions in one query:

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.

#AS — Naming Your Results

-- 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;

SELECT
  department,
  COUNT(*) AS employee_count,
  ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department;

#GROUP BY — Summarize Per Category

-- Average salary PER department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Instead of one average for all employees, you get one average per department:

#The Golden Rule of GROUP BY

-- 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.

#WHERE with GROUP BY

You can filter rows BEFORE grouping:

-- 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:

1. FROM. Start with all employees
2. WHERE — filter to only post-2020 hires
3. GROUP BY — split remaining rows into department groups
4. SELECT — calculate AVG for each group

Try it! Find the total salary per department, but only for employees earning more than 80000.

#HAVING — Filtering Groups

-- Departments with more than 2 employees
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

#WHERE vs HAVING

-- 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."

Try it! Find departments where the average salary exceeds 80000.

#The Complete Query Pipeline

Here is the full order of SQL clauses and when each one runs:

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

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.

#Aggregating Orders and Products

Let us practice with the other tables in the database:

-- 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.

#Practice Challenges

Test your skills with these progressively harder challenges:

1. Count the total number of orders
2. Find the average order amount
3. Find the highest and lowest salary in the employees table
4. Count employees per department and sort by count (highest first)
5. Find the average salary per department, but only show departments where the average is above 80000
6. Find customers who have placed more than 2 orders

#Key Takeaways

• 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)