D
D
Dmitry Polyashov2017-08-02 14:05:05
JavaScript
Dmitry Polyashov, 2017-08-02 14:05:05

SQL - list of bosses with salary over budget by department, with the amount of the excess?

https://habrahabr.ru/post/181033/
There are two tables, with departments and employees.
Required Output:
a list of bosses with salaries exceeding the budget for the department, with the amount of excess

Answer the question

In order to leave comments, you need to log in

5 answer(s)
H
hzzzzl, 2019-03-08
@orlyone

https://codepen.io/anon/pen/gEWgWb

T
TCloud, 2019-03-08
@OTCloud

It's simple.

let newText = "Текст для placeholder";

$( ".needed-input" ).attr( "placeholder", newText );

G
gill-sama, 2017-08-02
@polyashovdima

1. Choose the amount of income by department
2. It seems to me that the head (chief_id) of the department, as well as the budget (budget) should be kept with the department, if so, then add item 1 to the department and cut off the extra

select chief_id, t.sum-budget as overdraft from department d 
join
 (select sum(e.salary) as sum, d.id as d_id from employee e join department d on e.department_id = d.id group by d.id) t
 on t.d_id = d.id
where  t.sum>budget

Accordingly, we get the id of the boss and the difference between the sum of the RFP and the Department's Budget

D
Dmitry Polyashov, 2017-08-02
@polyashovdima

Right?

select a.*
from   department a
where  a.budget = ( select max(budget) from department b
                    where  b.budget = a.budget )

V
V Sh., 2017-08-02
@JuniorNoobie

SELECT id_chief, SUM(salary) - MaxSalaryToDepartments as diff
FROM employee
GROUP BY id_chief
HAVING SUM(salary) > MaxSalaryToDepartments

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question