I can't do SELECT FROM SELECT [MS SQL Server]
There is a table of 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 went so far as to find the maximum sum of
SELECT MAX(A.SUM_Money)
FROM (
SELECT Dep_number, SUM(Cash_bonus + Salary) AS SUM_Money
FROM Employees
GROUP BY Dep_number
) AS A
Only here I can not display the field next to the department number
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:
Message 8120, level 16, state 1, line 3
Column " A.Dep_number" is not valid in the selection list because it is not contained in either the statistical function or the GROUP BY clause.
Tell me what to do?
1 answers
In general, the grouping should be in the query that uses aggregation. So the query 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 need, because for each Dep_Number we have only one record in the request.
You can simply sort by SUM_Money in descending order and take the first entry:
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 may 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