task GROUP BY · medium
Payroll versus budget
For every department, return department, headcount, payroll (sum of salaries) and status: 'over' when payroll exceeds the budget, otherwise 'within'.
what to return
- Columns
departmentheadcountpayrollstatusin this order- Rows
- 6 rows · any order
- Watch for
- Departments with no employees show headcount 0 and payroll 0.
- Payroll equal to the budget is 'within'.
- Skill
- Report every group, including empty ones, and classify it. Refresher: Make decisions with CASE →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result6 rows · any order
| department | headcount | payroll | status |
|---|---|---|---|
| Engineering | 7 | 1000000 | within |
| Sales | 4 | 490000 | over |
| Finance | 2 | 215000 | within |
first 3 of 6 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
departments4 columns · 6 rows
| idinteger · key | nametext | locationtext | budgetreal |
|---|---|---|---|
| 1 | Engineering | Lisbon | 1100000 |
| 2 | Sales | Austin | 450000 |
| 3 | Finance | Austin | 250000 |
| 4 | People | Lisbon | 150000 |
| 5 | Design | Remote | 305000 |
showing 5 of 6 rows
employees7 columns · 20 rows
| idinteger · key | nametext | manager_idinteger → employees | department_idinteger → departments | titletext | salaryreal | hired_attext |
|---|---|---|---|---|---|---|
| 1 | Ada Moreno | NULL | NULL | CEO | 250000 | 2019-03-01 |
| 2 | Bruno Costa | 1 | 1 | VP Engineering | 180000 | 2019-06-15 |
| 3 | Carmen Diaz | 1 | 2 | VP Sales | 150000 | 2020-01-10 |
| 4 | Dev Patel | 2 | 1 | Engineering Manager | 140000 | 2020-05-04 |
| 5 | Elif Kaya | 4 | 1 | Senior Engineer | 145000 | 2021-02-01 |
showing 5 of 20 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.