0

Suppose a table contains employee id, department name and salary. I want to select the department that gives total salary greater than average salary given by all departments.

The solution that I know, is grouping data by department to find sum of salaries given by that department and then use having clause to compare the sum with the output of a subquery, that again groups by department and finds average department salary.

I want to know if it is possible to group according to departments once, and use this grouped data multiple times so that we don't have to group again and again.

  • I am not sure about the database, but its probably some oracle version. I just started learning SQL and the database is provided by our college so I am not very sure – HIMANK Jan 26 '16 at 20:08
1

The following will get you the assignment answer. There are many ways to solve this, and it is not necessarily bad to group several times in sub-queries. Most RDBMS engines will be able to optimize your query (some) and performance is likely not an issue for this particular question.

SELECT TOP (1)
     Department
    ,SumSalary
    ,AvgSalary
FROM
(
SELECT
     Department
     ,SumSalary
    ,AVG(SumSalary) OVER(PARTITION BY NULL) AS AvgSalary
FROM
(
SELECT
     Department
    ,SUM(Salary) AS SumSalary
FROM DIM.Property
GROUP BY Department
) SubQuery
) x
WHERE SumSalary > AvgSalary
ORDER BY SumSalary DESC

I'd suggest that you read up on Window Functions so that you can fully explain any work submitted (there are plenty of sites and technical documentation for each DB and ANSI SQL).

Also note that a TOP (1) gets just the single highest paying department that is above the average, since you may have many that meet this criteria. To ensure you get the highest paying the ORDER BY guarantees the highest paying is on top. You still have the possibility of two departments paying the same amount though in which case you have a non-deterministic query (not guaranteed to get the same department every time). You need a tie-breaker unless you accept this potential for random results or it is guaranteed not to happen by some other business process or rule (yeah right!).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.