COUNT Function

-- Count all rows in a table
SELECT COUNT(*) AS total_employees
FROM employees;

-- Count non-NULL values in a specific column
SELECT COUNT(email) AS employees_with_email,
       COUNT(*) - COUNT(email) AS missing_email
FROM employees;

COUNT with GROUP BY

-- Count employees per department
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

-- Count unique values per group
SELECT department, COUNT(DISTINCT job_title) AS unique_roles
FROM employees
GROUP BY department;

SUM Function

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

-- Department budgets
SELECT department, SUM(salary) AS department_cost
FROM employees
GROUP BY department
ORDER BY department_cost DESC;

-- Sum with conditions
SELECT department,
       SUM(CASE WHEN status = 'active' THEN salary ELSE 0 END) AS active_cost,
       SUM(CASE WHEN status = 'terminated' THEN salary ELSE 0 END) AS terminated_cost
FROM employees
GROUP BY department;

AVG Function

-- Company-wide average salary
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;

-- Rounded averages for readability
SELECT department,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;

Combining COUNT, SUM, and AVG

-- Comprehensive department report
SELECT department,
       COUNT(*) AS headcount,
       SUM(salary) AS total_cost,
       ROUND(AVG(salary), 2) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY headcount DESC;

COUNT with Conditional Logic

-- Count active vs inactive employees per department
SELECT department,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active,
       SUM(CASE WHEN status = 'terminated' THEN 1 ELSE 0 END) AS inactive
FROM employees
GROUP BY department;

-- Percentage calculation
SELECT department,
       COUNT(*) AS total,
       ROUND(SUM(CASE WHEN hire_date >= '2023-01-01' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS new_hire_pct
FROM employees
GROUP BY department;

SUM with JOIN

-- Total sales per customer
SELECT c.customer_name, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_spent DESC;

AVG with Window Functions

-- Compare each department's average to company average
SELECT department,
       AVG(salary) AS dept_avg,
       AVG(AVG(salary)) OVER () AS company_avg,
       AVG(salary) - AVG(AVG(salary)) OVER () AS difference
FROM employees
GROUP BY department;

NULL Handling Deep Dive

-- Demonstrate NULL behavior
SELECT COUNT(*) AS total_rows,
       COUNT(commission_pct) AS non_null_commissions,
       SUM(commission_pct) AS total_commission,
       AVG(commission_pct) AS avg_commission
FROM employees;

Common Mistakes

-- Wrong: This excludes rows with NULL commission
SELECT COUNT(commission_pct) FROM employees;

-- Right: This counts all rows
SELECT COUNT(*) FROM employees;

-- Safe division for percentages
SELECT department,
       CASE WHEN COUNT(*) > 0
           THEN ROUND(SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
           ELSE 0
       END AS female_pct
FROM employees
GROUP BY department;

Performance Considerations