Aggregate: Summary Report

basic

Department summary with average salary and headcount

SELECT

department,

COUNT(*) AS employee_count,

AVG(salary) AS avg_salary,

SUM(salary) AS total_payroll

FROM employees

GROUP BY department;

department | employee_count | avg_salary | total_payroll

Engineering | 45 | 95000 | 4275000

Sales | 30 | 75000 | 2250000

GROUP BY collapses 75 employees into 2 summary rows. Loses individual employee detail.

All

Window: Detail with Context

intermediate

Show each employee with their department average for comparison

SELECT

employee_name,

department,

salary,

AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,

salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg

FROM employees

ORDER BY department, salary DESC;

employee_name | department | salary | dept_avg_salary | diff_from_avg

Alice | Engineering | 120000 | 95000 | 25000

Bob | Engineering | 95000 | 95000 | 0

Carol | Engineering | 70000 | 95000 | -25000

Window function preserves all employee rows while adding department average context. Shows how each employee compares to department average.

All

Comparison: Same Calculation, Different Output

intermediate

Calculate department average using both approaches

-- Aggregate approach (summary)

SELECT

department,

AVG(salary) AS avg_salary

FROM employees