How to use a where statement on a calculated column while grouping by another column-CodePudding

I'm trying to return a list with each department, count of employees in each, and the salary range for each. When I do that it works just fine:

SELECT DISTINCT Dept, COUNT(*) AS 'Employee_Count', (MAX(Salary)-MIN(Salary)) AS 'Salary_Range'
    FROM employeebig
    GROUP BY Dept;

But when I try to filter using a Where statement it returns an empty list

SELECT DISTINCT Dept, COUNT(*) AS 'Employee_Count', (MAX(Salary)-MIN(Salary)) AS 'Salary_Range'
    FROM employeebig
    WHERE @Salary_Range > 3000
    GROUP BY Dept

SELECT DISTINCT Dept, COUNT(*) AS 'Employee_Count', (MAX(Salary)-MIN(Salary)) AS 'Salary_Range'
    FROM employeebig
    WHERE 'Salary_Range' > 3000
    GROUP BY Dept

So I'm guessing I'm referencing the range wrong. And is there a better way to return the range instead of max-min?

CodePudding user response：

You should do the filtering "after" the grouping. For that you can use the "Having" Clause, like this:

SELECT Dept, 
       COUNT(*) AS 'Employee_Count', 
       (MAX(Salary)-MIN(Salary)) AS 'Salary_Range'
FROM employeebig
GROUP BY Dept
HAVING (MAX(Salary)-MIN(Salary)) > 3000

The Distinct is not necesary since you are already grouping by "Dept".

CodePudding user response：

Try This:

select x.Dept,x.Employee_Count,x.Salary_Range
from(
    SELECT Dept, COUNT(*) AS 'Employee_Count', 
    (MAX(Salary)-MIN(Salary)) AS 'Salary_Range'
    FROM employeebig
    GROUP BY Dept
) x HAVING x.Salary_Range>3000;