What Are Aggregate Functions?

-- Basic aggregate function usage
SELECT COUNT(*) AS total_employees
FROM employees;

-- Aggregation with GROUP BY
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Core Aggregate Functions

COUNT Function

-- Count all rows
SELECT COUNT(*) AS total FROM employees;

-- Count non-null values
SELECT COUNT(phone_number) AS with_phone FROM employees;

-- Count distinct values
SELECT COUNT(DISTINCT department) AS unique_departments FROM employees;

SUM Function

-- Total salary for all employees
SELECT SUM(salary) AS total_payroll FROM employees;

-- Total salary per department
SELECT department, SUM(salary) AS department_total
FROM employees
GROUP BY department;

AVG Function

-- Average salary company-wide
SELECT AVG(salary) AS average_salary FROM employees;

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

MIN and MAX Functions

-- Salary range
SELECT MIN(salary) AS lowest_salary,
       MAX(salary) AS highest_salary
FROM employees;

-- Per department
SELECT department,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department;

Combining Multiple Aggregates

-- Single query with multiple aggregate functions
SELECT department,
       COUNT(*) AS headcount,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY headcount DESC;

Aggregate Functions with CASE

-- Conditional aggregation
SELECT department,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
       SUM(CASE WHEN status = 'terminated' THEN 1 ELSE 0 END) AS terminated_count
FROM employees
GROUP BY department;

-- Percentage calculations with aggregates
SELECT department,
       COUNT(*) AS total,
       ROUND(SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS female_pct
FROM employees
GROUP BY department;

Aggregate Functions with DISTINCT

-- Count unique departments
SELECT COUNT(DISTINCT department) AS unique_depts FROM employees;

-- Average of distinct salaries (rarely useful, but possible)
SELECT AVG(DISTINCT salary) AS avg_distinct_salary FROM employees;

NULL Handling in Aggregates

-- COUNT(*) counts all rows including NULLs
SELECT COUNT(*) AS total_rows FROM employees;

-- COUNT(column) excludes NULLs
SELECT COUNT(commission_pct) AS non_null_commissions FROM employees;

-- SUM and AVG ignore NULLs automatically
SELECT AVG(bonus) AS avg_bonus FROM employees;

Common Aggregate Patterns