Mysql salary > 100000-CodePudding

my code Should only show avgSalary > 10000; but i cant implement it into code :

SELECT dept_name, AVG(salary) AS AvgSalary 
FROM instructor
GROUP BY dept_name
ORDER BY AvgSalary ASC;

which gives result

Dept_name:    Salary:
Accounting  48716.592500
Languages   57421.856667
Psychology  61143.050000
Biology 61287.250000
Statistics  67795.441667
English 72089.050000
Elec. Eng.  74162.740000
Athletics   77098.198000
Astronomy   79070.080000
Mech. Eng.  79813.020000
Marketing   84097.437500
Cybernetics 96346.567500
Comp. Sci.  98133.470000
Geology 99382.590000
Pol. Sci.   100053.073333
Finance 105311.380000
Physics 114576.900000

CodePudding user response：

You can go with a sub select like:

SELECT i.dept_name, i.AvgSalary
FROM
(
SELECT
dept_name, AVG(Salary) as AvgSalary
FROM instructor
) AS i
where i.AvgSalary > 100000
GROUP BY i.dept_name
ORDER BY i.AvgSalary ASC;

CodePudding user response：

Since you want to apply a filter on the result of a group by, you can't use a where condition. You can either use a subquery:

SELECT dept_name, AvgSalary
FROM (
    SELECT dept_name, AVG(salary) AS AvgSalary 
    FROM instructor
    GROUP BY dept_name
) department_salaries
WHERE AvgSalary > 10000
ORDER BY AvgSalary ASC

or the HAVING clause, which is like WHERE but applies to the results of aggregation:

SELECT dept_name, AVG(salary) AS AvgSalary 
FROM instructor
GROUP BY dept_name
HAVING AvgSalary > 10000
ORDER BY AvgSalary ASC