Project Budgets
Medium
Given the database with the schema shown below, write a query to fetch each project's ID, title, budget, total number of employees assigned to the project, and the sum of their salaries.
employees projects +---------------+---------+ +---------------+---------+ | id | int |<----+ +->| id | int | | first_name | varchar | | | | title | varchar | | last_name | varchar | | | | start_date | date | | salary | int | | | | end_date | date | | department_id | int |--+ | | | budget | int | +---------------+---------+ | | | +---------------+---------+ | | | departments | | | employees_projects +---------------+---------+ | | | +---------------+---------+ | id | int |<-+ | +--| project_id | int | | name | varchar | +-----| employee_id | int | +---------------+---------+ +---------------+---------+
Your query should output a result in the following format, ordered by highest to lowest total salary:
id | title | budget | num_employees | total_salaries ----+---------+--------+---------------+---------------- int | varchar | int | int | int
Our solution will make use of several aggregate functions like SUM and COUNT to get the number of employees and total salaries. We'll also make use of LEFT JOIN to combine data from the employees and projects table via the junction table employees_projects.
Here's our full solution:
select
p.id,
p.title,
p.budget,
count(e.id) as num_employees,
sum(e.salary) as total_salaries
from
projects p
left join
employees_projects ep
on
p.id = ep.project_id
left join
employees e
on
ep.employee_id = e.id
group by
p.id,
p.title,
p.budget
order by
p.id;Our query will return a table of results in this format:
id | title | budget | num_employees | total_salaries ----+--------------------------+---------+---------------+---------------- 1 | Build a cool site | 1000000 | 3 | 130000 2 | Update TPS Reports | 100000 | 1 | 20000 3 | Design 3 New Silly Walks | 100 | 1 | 10000
-- Write your query here select p.id, p.title, p.budget, count(e.id) as num_employees, sum(e.salary) as total_salaries from projects p join employees_projects ep on p.id = ep.project_id join employees e on ep.employee_id = e.id group by 1 order by 5 desc;