There is a table called Employees and Departments.
It is necessary to find such departments in which the total salary of employees will be the highest.
Here is the content of the tables themselves
I only got to the point where I found the maximum amount
SELECT MAX (A.SUM_Money)
FROM (
SELECT Dep_number, SUM (Cash_bonus + Salary) AS SUM_Money
FROM Employees
GROUP BY Dep_number
) AS A
But I just can’t display the field with the department number next to it
SELECT A.Dep_number, MAX (A.SUM_Money)
FROM (
SELECT Dep_number, SUM (Cash_bonus + Salary) AS SUM_Money
FROM Employees
GROUP BY Dep_number
) AS A
Writes an error:
Msg 8120 Level 16 State 1 Line 3
Column “A.Dep_number” is invalid in the select list because it is not contained in either an aggregate function or a GROUP BY clause.
Tell me what to do?
Answer 1, authority 100%
In general, the grouping should be in the query in which the aggregation is used. So the request will be like this:
SELECT A.Dep_number, MAX (A.SUM_Money)
FROM (
SELECT Dep_number, SUM (Cash_bonus + Salary) AS SUM_Money
FROM Employees
GROUP BY Dep_number
) AS A
GROUP BY A.Dep_Number.
But this is not what we want, because for each Dep_Number we have only one record in our request.
You can just sort by SUM_Money in descending order and take the first record:
SELECT TOP 1 A.Dep_number, A.SUM_Money
FROM (
SELECT Dep_number, SUM (Cash_bonus + Salary) AS SUM_Money
FROM Employees
GROUP BY Dep_number
) AS A
ORDER BY A.SUM_Money DESC
But there can be several departments with the same amount. So let’s add the ranking to the subquery:
SELECT A.Dep_number, A.SUM_Money
FROM (
SELECT rank () over (order by sum (cash_bonus + Salary) desc) rank,
Dep_number, SUM (Cash_bonus + Salary) AS SUM_Money
FROM Employees
GROUP BY Dep_number
) AS A
where rank = 1