MediumSQL challenge #109GROUP BYAVGMINMAX

Salary Statistics per Department: Count, Avg, Min and Max

For each department, show headcount, average, lowest and highest salary, and the range between them.

The challenge runs on the SQLabHub practice database (SQLite) and uses the employees table. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

For each department, show headcount, average salary, min and max salary.

A correct answer returns 10 rows with 6 columns: department, headcount, avg_salary, min_salary, max_salary, salary_range.

Tables used in this challenge

`employees` 120 rows

id INTEGER (PK) · first_name TEXT · last_name TEXT · email TEXT · department TEXT · department_id INTEGER · title TEXT · salary REAL · hire_date TEXT · manager_id INTEGER · city TEXT · status TEXT

First 3 rows of the employees table
id	first_name	last_name	email	department	department_id	title	salary	hire_date	manager_id	city	status
1	William	Lewis	wlewis18@corp.io	Customer Success	7	Senior CS Manager	61774	2022-07-24	NULL	San Diego	active
2	John	Miller	jmiller51@corp.io	Legal	8	Paralegal	191694	2021-08-09	NULL	San Diego	active
3	Daniel	Rivera	drivera50@corp.io	Engineering	1	VP Engineering	135067	2016-06-06	NULL	Houston	active

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
department	headcount	avg_salary	min_salary	max_salary	salary_range
Customer Success	15	134140	61774	203999	142225
Design	10	133965	46044	194543	148499
Engineering	11	131028	51465	199029	147564
Operations	13	126879	54387	203467	149080
HR	12	124151	50360	191037	140677
Marketing	13	124111	62910	204449	141539
Finance	10	121471	50463	196032	145569
Product	9	119153	64930	188273	123343
Sales	11	116893	52164	195759	143595
Legal	16	112866	72975	192220	119245

The complete result: 10 rows. This is the output of the model solution on the practice database.

What you practise

Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
Grouping rows with GROUP BY to get one result row per group
Sorting the result with ORDER BY

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

GROUP BY department and use AVG, MIN, MAX on salary

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

Read from the employees table.
Group the rows by department, so the result has one row for each value.
For each group, compute COUNT(*), AVG(salary), MIN(salary) and MAX(salary).
ROUND() trims the calculated figures to a fixed number of decimals.
Sort the result by avg_salary descending.

Model solution

Checking your session…

What to watch for

Several aggregates can share one GROUP BY, and they can be combined in an expression: the range is MAX(salary) - MIN(salary) computed per department.

GROUP BY. Every selected column must either be in GROUP BY or sit inside an aggregate. SQLite accepts other columns and returns an arbitrary value for them; most databases raise an error.
ORDER BY. Without ORDER BY the row order is not guaranteed, even if it looks stable. Rows that tie on the sort columns can come back in any order.
Aggregate functions. Aggregates skip NULLs. AVG divides by the number of non-NULL values and COUNT(column) can be smaller than COUNT(*).