Sales by Department

basic

Management needs employee count and average salary per department

SELECT

department,

COUNT(*) AS employee_count,

AVG(salary) AS avg_salary,

SUM(salary) AS total_payroll

FROM employees

GROUP BY department

ORDER BY total_payroll DESC;

department | employee_count | avg_salary | total_payroll

Engineering | 45 | 95000.00 | 4275000.00

Sales | 30 | 75000.00 | 2250000.00

Marketing | 25 | 68000.00 | 1700000.00

GROUP BY department creates one row per department. Aggregates computed for each group.

All

Multi-Column Grouping

intermediate

HR analyzes employee distribution by department and job title

SELECT

department,

job_title,

COUNT(*) AS employee_count,

AVG(salary) AS avg_salary

FROM employees

GROUP BY department, job_title

ORDER BY department, employee_count DESC;

department | job_title | employee_count | avg_salary

Engineering | Senior Engineer | 20 | 110000.00

Engineering | Engineer | 15 | 85000.00

Sales | Account Manager | 18 | 80000.00

GROUP BY multiple columns creates groups for each unique combination. More granular than single column grouping.

All