SQL Aggregate Functions & GROUP BY Tutorial

SQL Aggregate Functions & GROUP BY Tutorial: Summarize Data

📖 Introduction: From Rows to Insights

So far, you've learned to filter, sort, and limit individual rows. But real business questions need summaries:

"How many employees work in each department?"
"What's the average salary?"
"What's our total sales by region?"

Aggregate functions take many rows and return a single calculated value. GROUP BY lets you calculate these values for each group (like each department or each region).

Analogy: If SELECT is like reading every line of a book, aggregates are like reading the summary at the end of each chapter.

🛠️ Setting Up Practice Data

Table 1: `employees` (for basic aggregates)

sql
1CREATE TABLE employees (
2    emp_id INT PRIMARY KEY,
3    first_name VARCHAR(50),
4    last_name VARCHAR(50),
5    department VARCHAR(50),
6    salary DECIMAL(10,2),
7    hire_date DATE,
8    city VARCHAR(50)
9);
10
11INSERT INTO employees VALUES
12(1, 'Alice', 'Johnson', 'IT', 75000.00, '2020-03-15', 'New York'),
13(2, 'Bob', 'Smith', 'HR', 55000.00, '2019-07-22', 'Los Angeles'),
14(3, 'Carol', 'Williams', 'IT', 80000.00, '2021-01-10', 'New York'),
15(4, 'David', 'Brown', 'Finance', 90000.00, '2018-11-05', 'Chicago'),
16(5, 'Eve', 'Davis', 'HR', 55000.00, '2022-06-18', 'Los Angeles'),
17(6, 'Frank', 'Miller', 'IT', 75000.00, '2020-09-30', 'New York'),
18(7, 'Grace', 'Lee', 'Finance', 85000.00, '2020-05-12', 'Chicago'),
19(8, 'Henry', 'Wilson', 'Sales', 60000.00, '2021-08-01', 'Miami'),
20(9, 'Ivy', 'Taylor', 'Sales', 65000.00, '2019-03-25', 'Miami'),
21(10, 'Jack', 'Anderson', 'IT', 72000.00, '2022-01-15', 'New York');

Table 2: `sales` (for the hands-on project)

sql
1CREATE TABLE sales (
2    sale_id INT PRIMARY KEY,
3    region VARCHAR(50),
4    product VARCHAR(50),
5    amount DECIMAL(10,2),
6    sale_date DATE,
7    salesperson VARCHAR(50)
8);
9
10INSERT INTO sales VALUES
11(1, 'North', 'Laptop', 1200.00, '2024-01-15', 'Alice'),
12(2, 'South', 'Mouse', 25.00, '2024-01-16', 'Bob'),
13(3, 'North', 'Keyboard', 150.00, '2024-01-17', 'Alice'),
14(4, 'East', 'Monitor', 300.00, '2024-01-18', 'Carol'),
15(5, 'South', 'Laptop', 1200.00, '2024-01-19', 'Bob'),
16(6, 'North', 'Mouse', 25.00, '2024-01-20', 'David'),
17(7, 'West', 'Keyboard', 150.00, '2024-01-21', 'Eve'),
18(8, 'East', 'Laptop', 1200.00, '2024-01-22', 'Carol'),
19(9, 'North', 'Monitor', 300.00, '2024-01-23', 'Alice'),
20(10, 'South', 'Keyboard', 150.00, '2024-01-24', 'Bob'),
21(11, 'West', 'Monitor', 300.00, '2024-01-25', 'Eve'),
22(12, 'East', 'Mouse', 25.00, '2024-01-26', 'Frank');

1️⃣ COUNT — Count Rows

Purpose: Count the total number of rows, or non-NULL values in a column.

Syntax:

sql
1SELECT COUNT(*) FROM table_name;
2SELECT COUNT(column_name) FROM table_name;

Example 1: Count All Employees

sql
1SELECT COUNT(*) AS total_employees FROM employees;

Output:

+-----------------+
| total_employees |
+-----------------+
| 10              |
+-----------------+

COUNT(*) counts every row, including those with NULL values.

Example 2: Count Employees in a Specific Department

sql
1SELECT COUNT(*) AS it_employees 
2FROM employees 
3WHERE department = 'IT';

Output:

+--------------+
| it_employees |
+--------------+
| 4            |
+--------------+

Example 3: COUNT vs COUNT(column)

sql
1SELECT 
2    COUNT(*) AS total_rows,
3    COUNT(city) AS cities_with_value,
4    COUNT(DISTINCT city) AS unique_cities
5FROM employees;

Output:

+------------+-------------------+---------------+
| total_rows | cities_with_value | unique_cities |
+------------+-------------------+---------------+
| 10         | 10                | 4             |
+------------+-------------------+---------------+

Function	What It Counts
`COUNT(*)`	All rows
`COUNT(column)`	Rows where column is not NULL
`COUNT(DISTINCT column)`	Unique non-NULL values

2️⃣ SUM — Add Up Values

Purpose: Calculate the total of a numeric column.

Syntax:

sql
1SELECT SUM(column_name) FROM table_name;

Example 4: Total Salary Expense

sql
1SELECT SUM(salary) AS total_salary_expense FROM employees;

Output:

+----------------------+
| total_salary_expense |
+----------------------+
| 667000.00            |
+----------------------+

Example 5: Total Sales Amount

sql
1SELECT SUM(amount) AS total_sales FROM sales;

Output:

+-------------+
| total_sales |
+-------------+
| 5825.00     |
+-------------+

Example 6: SUM with WHERE

sql
1SELECT SUM(salary) AS it_salary_total 
2FROM employees 
3WHERE department = 'IT';

Output:

+-----------------+
| it_salary_total |
+-----------------+
| 302000.00       |
+-----------------+

Note: SUM() only works with numeric columns. Using it on text will cause an error.

3️⃣ AVG — Calculate Average

Purpose: Find the average (mean) value of a numeric column.

Syntax:

sql
1SELECT AVG(column_name) FROM table_name;

Example 7: Average Salary

sql
1SELECT AVG(salary) AS average_salary FROM employees;

Output:

+----------------+
| average_salary |
+----------------+
| 66700.000000   |
+-----------------+

Example 8: Average with Rounding

sql
1SELECT ROUND(AVG(salary), 2) AS average_salary FROM employees;

Output:

+----------------+
| average_salary |
+----------------+
| 66700.00       |
+----------------+

ROUND() is a helper function that rounds the result to 2 decimal places.

Example 9: Average Sales by Condition

sql
1SELECT AVG(amount) AS avg_laptop_sale 
2FROM sales 
3WHERE product = 'Laptop';

Output:

+-----------------+
| avg_laptop_sale |
+-----------------+
| 1200.00         |
+-----------------+

4️⃣ MAX & MIN — Find Extremes

Purpose: Find the highest (MAX) and lowest (MIN) value in a column.

Syntax:

sql
1SELECT MAX(column_name) FROM table_name;
2SELECT MIN(column_name) FROM table_name;

Example 10: Highest and Lowest Salary

sql
1SELECT 
2    MAX(salary) AS highest_salary,
3    MIN(salary) AS lowest_salary
4FROM employees;

Output:

+----------------+---------------+
| highest_salary | lowest_salary |
+----------------+---------------+
| 90000.00       | 55000.00      |
+----------------+---------------+

Example 11: Most Recent Hire Date

sql
1SELECT MAX(hire_date) AS most_recent_hire FROM employees;

Output:

+------------------+
| most_recent_hire |
+------------------+
| 2022-06-18       |
+------------------+

Tip: MAX() and MIN() work on dates and text too! MAX('Apple', 'Banana') returns 'Banana' (alphabetically last).

Example 12: Highest Sale Amount

sql
1SELECT MAX(amount) AS biggest_sale FROM sales;

Output:

+--------------+
| biggest_sale |
+--------------+
| 1200.00      |
+--------------+

5️⃣ GROUP BY — Summarize by Category

Purpose: Split data into groups and apply aggregate functions to each group separately.

Syntax:

sql
1SELECT column1, aggregate_function(column2)
2FROM table_name
3GROUP BY column1;

Golden Rule: Every column in your SELECT that is not inside an aggregate function must be in your GROUP BY.

Example 13: Count Employees Per Department

sql
1SELECT 
2    department, 
3    COUNT(*) AS emp_count
4FROM employees
5GROUP BY department;

Logic: Split employees into groups by department, then count each group.

Output:

+------------+-----------+
| department | emp_count |
+------------+-----------+
| Finance    | 2         |
| HR         | 2         |
| IT         | 4         |
| Sales      | 2         |
+------------+-----------+

Example 14: Average Salary Per Department

sql
1SELECT 
2    department,
3    COUNT(*) AS emp_count,
4    ROUND(AVG(salary), 2) AS avg_salary
5FROM employees
6GROUP BY department;

Output:

+------------+-----------+------------+
| department | emp_count | avg_salary |
+------------+-----------+------------+
| Finance    | 2         | 87500.00   |
| HR         | 2         | 55000.00   |
| IT         | 4         | 75500.00   |
| Sales      | 2         | 62500.00   |
+------------+-----------+------------+

Example 15: Total Sales Per Region

sql
1SELECT 
2    region,
3    COUNT(*) AS total_orders,
4    SUM(amount) AS total_revenue
5FROM sales
6GROUP BY region;

Output:

+--------+--------------+---------------+
| region | total_orders | total_revenue |
+--------+--------------+---------------+
| East   | 3            | 1525.00       |
| North  | 4            | 1675.00       |
| South  | 3            | 1375.00       |
| West   | 2            | 450.00        |
+--------+--------------+---------------+

Example 16: Multiple Grouping Columns

sql
1SELECT 
2    region,
3    product,
4    COUNT(*) AS units_sold,
5    SUM(amount) AS revenue
6FROM sales
7GROUP BY region, product;

Output:

+--------+----------+------------+----------+
| region | product  | units_sold | revenue  |
+--------+----------+------------+----------+
| East   | Laptop   | 1          | 1200.00  |
| East   | Monitor  | 1          | 300.00   |
| East   | Mouse    | 1          | 25.00    |
| North  | Keyboard | 1          | 150.00   |
| North  | Laptop   | 1          | 1200.00  |
| North  | Monitor  | 1          | 300.00   |
| North  | Mouse    | 1          | 25.00    |
| South  | Keyboard | 1          | 150.00   |
| South  | Laptop   | 1          | 1200.00  |
| South  | Mouse    | 1          | 25.00    |
| West   | Keyboard | 1          | 150.00   |
| West   | Monitor  | 1          | 300.00   |
+--------+----------+------------+----------+

6️⃣ HAVING — Filter Groups (Not Rows!)

Purpose: Filter groups after GROUP BY has run. WHERE filters rows before grouping; HAVING filters groups after grouping.

Syntax:

sql
1SELECT column, aggregate(column)
2FROM table
3GROUP BY column
4HAVING condition;

Key Difference:

WHERE filters individual rows (before aggregation)

HAVING filters groups (after aggregation)

Example 17: Departments with More Than 2 Employees

sql
1SELECT 
2    department,
3    COUNT(*) AS emp_count
4FROM employees
5GROUP BY department
6HAVING COUNT(*) > 2;

Output:

+------------+-----------+
| department | emp_count |
+------------+-----------+
| IT         | 4         |
+------------+-----------+

Why not WHERE COUNT(*) > 2? Because WHERE runs before counting. You can't filter on a count that doesn't exist yet! Use HAVING for aggregate conditions.

Example 18: Regions with Total Sales Over $1000

sql
1SELECT 
2    region,
3    SUM(amount) AS total_revenue
4FROM sales
5GROUP BY region
6HAVING SUM(amount) > 1000;

Output:

+--------+---------------+
| region | total_revenue |
+--------+---------------+
| East   | 1525.00       |
| North  | 1675.00       |
| South  | 1375.00       |
+--------+---------------+

West ( $450) is excluded because its total is less than$ 1000.

Example 19: WHERE + GROUP BY + HAVING Together

sql
1SELECT 
2    department,
3    COUNT(*) AS emp_count,
4    ROUND(AVG(salary), 2) AS avg_salary
5FROM employees
6WHERE hire_date >= '2020-01-01'      -- Filter rows first
7GROUP BY department
8HAVING COUNT(*) >= 2                  -- Filter groups after
9ORDER BY avg_salary DESC;             -- Sort the final result

Logic Flow:

WHERE: Keep only employees hired since 2020
GROUP BY: Group the remaining employees by department
HAVING: Keep only departments with 2+ employees in that filtered set
ORDER BY: Sort by average salary, highest first

Output:

+------------+-----------+------------+
| department | emp_count | avg_salary |
+------------+-----------+------------+
| Finance    | 2         | 87500.00   |
| IT         | 4         | 75500.00   |
| HR         | 2         | 55000.00   |
+------------+-----------+------------+

📝 Complete Command Reference

Function	Purpose	Example
`COUNT(*)`	Count all rows	`SELECT COUNT(*) FROM employees;`
`COUNT(column)`	Count non-NULL values	`SELECT COUNT(city) FROM employees;`
`SUM(column)`	Total of numeric column	`SELECT SUM(salary) FROM employees;`
`AVG(column)`	Average of numeric column	`SELECT AVG(salary) FROM employees;`
`MAX(column)`	Highest value	`SELECT MAX(salary) FROM employees;`
`MIN(column)`	Lowest value	`SELECT MIN(salary) FROM employees;`
`GROUP BY`	Group rows for aggregation	`GROUP BY department`
`HAVING`	Filter groups after aggregation	`HAVING COUNT(*) > 2`

🚀 Hands-On Project: Sales Summary Report by Region

Project Goal

Generate a comprehensive regional sales report for management.

Requirements

Total revenue per region
Number of orders per region
Average order value per region
Only include regions with 2+ orders
Sort by total revenue (highest first)

Solution

sql
1SELECT 
2    region,
3    COUNT(*) AS total_orders,
4    SUM(amount) AS total_revenue,
5    ROUND(AVG(amount), 2) AS avg_order_value,
6    MIN(amount) AS smallest_order,
7    MAX(amount) AS biggest_order
8FROM sales
9GROUP BY region
10HAVING COUNT(*) >= 2
11ORDER BY total_revenue DESC;

Output:

+--------+--------------+---------------+-----------------+---------------+---------------+
| region | total_orders | total_revenue | avg_order_value | smallest_order| biggest_order |
+--------+--------------+---------------+-----------------+---------------+---------------+
| North  | 4            | 1675.00       | 418.75          | 25.00         | 1200.00       |
| East   | 3            | 1525.00       | 508.33          | 25.00         | 1200.00       |
| South  | 3            | 1375.00       | 458.33          | 25.00         | 1200.00       |
| West   | 2            | 450.00        | 225.00          | 150.00        | 300.00        |
+--------+--------------+---------------+-----------------+---------------+---------------+

Bonus: Product Performance Within Each Region

sql
1SELECT 
2    region,
3    product,
4    COUNT(*) AS units_sold,
5    SUM(amount) AS product_revenue
6FROM sales
7GROUP BY region, product
8HAVING SUM(amount) > 100
9ORDER BY region, product_revenue DESC;

✅ Module 4 Summary

Concept	What It Does	Key Point
`COUNT(*)`	Count rows	Use `*` for all rows
`SUM()`	Add values	Numeric columns only
`AVG()`	Calculate mean	Returns many decimals; use `ROUND()`
`MAX()` / `MIN()`	Find extremes	Works on numbers, dates, and text
`GROUP BY`	Group rows	Non-aggregated columns must be in GROUP BY
`HAVING`	Filter groups	Use for conditions on aggregates
`WHERE`	Filter rows	Use for conditions on individual rows

🎯 Practice Exercises

Count how many employees are in each city.
Find the total salary paid by each department.
Calculate the average salary per city.
Find the highest and lowest sale amount in the sales table.
Show departments where the average salary is above $70,000.
Show regions where total revenue exceeds $1000, sorted by revenue.
Count how many unique products were sold in each region.
Find the top salesperson by total sales amount.

🎓 What's Next?

In Module 5, you'll learn Data Manipulation Language (DML) — how to insert, update, and delete data using INSERT, UPDATE, and DELETE. You'll build a full CRUD (Create, Read, Update, Delete) system! 🚀