Multiple Aggregates: Department Analysis

basic

HR needs comprehensive department metrics: headcount, salary costs, and ranges

SELECT

department,

COUNT(*) AS employee_count,

SUM(salary) AS total_payroll,

AVG(salary) AS avg_salary,

MIN(salary) AS min_salary,

MAX(salary) AS max_salary

FROM employees

GROUP BY department

ORDER BY total_payroll DESC;

department | employee_count | total_payroll | avg_salary | min_salary | max_salary

Engineering | 45 | 4275000 | 95000 | 75000 | 150000

Sales | 30 | 2250000 | 75000 | 50000 | 120000

Six aggregates provide comprehensive salary analysis per department. All computed in one pass.

All

Multi-Column Grouping: Department + Job Title

intermediate

HR analyzes compensation by department and job level for pay equity

SELECT

department,

job_title,

COUNT(*) AS headcount,

AVG(salary) AS avg_salary,

MIN(salary) AS min_salary,

MAX(salary) AS max_salary

FROM employees

WHERE employment_status = 'active'

GROUP BY department, job_title

HAVING COUNT(*) >= 2

ORDER BY department, avg_salary DESC;

department | job_title | headcount | avg_salary | min_salary | max_salary

Engineering | Senior | 20 | 110000 | 95000 | 150000

Engineering | Junior | 15 | 75000 | 65000 | 85000

Sales | Manager | 8 | 95000 | 85000 | 120000

GROUP BY department, job_title creates finer-grained groups. HAVING ensures statistical validity.

All

Time-Based Grouping: Monthly Customer Analysis