Is there a way to used grouped data multiple times in a query 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.
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 &gt; 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!).