CODEWITHSUNDEEP
mysql
Mark Henry
Mark Henry

Reputation: 2699

Outlier query with mysql using group by

I use the query below to detect a 10 centimeter difference between the average snowlevel and the minimum snowlevel. But PhpMyadmin says it is an invalid use of the group function. Can't see what is wrong. Do you know it?

SELECT lud, res_id, resort, 
MIN(snow_valley) as snow_val_min, 
MAX(snow_valley) as snow_val_max,
ROUND(AVG(snow_mountain),0) as snow_mnt_avg, 
MIN(snow_mountain) as snow_mnt_min
from sv_condition_sw 
where (lud ='2011-12-31')  AND (AVG(snow_mountain)-MIN(snow_mountain)>10) GROUP by res_id

Upvotes: 2

Views: 277

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115560

The reason for the error is that the order of execution of an SQL query is FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. So, the AVG() and MIN() you have in the WHERE are not evaluated until after the GROUP BY.

Use those conditions that have aggreagte functions in HAVING, not in WHERE: 

SELECT lud, res_id, resort, 
    MIN(snow_valley) AS snow_val_min, 
    MAX(snow_valley) AS snow_val_max,
    ROUND(AVG(snow_mountain),0) AS snow_mnt_avg, 
    MIN(snow_mountain) AS snow_mnt_min
FROM sv_condition_sw 
WHERE (lud ='2011-12-31')  
GROUP by res_id
HAVING (AVG(snow_mountain)-MIN(snow_mountain)>10)

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726659

Every column in your select with group by must be either aggregated on, or included in group by. Conditions on aggregated expressions must appear in the having clause, like this:

SELECT lud, res_id, resort, 
MIN(snow_valley) as snow_val_min, 
MAX(snow_valley) as snow_val_max,
ROUND(AVG(snow_mountain),0) as snow_mnt_avg, 
MIN(snow_mountain) as snow_mnt_min
from sv_condition_sw 
where (lud ='2011-12-31')
GROUP by lud, res_id, resort
HAVING AVG(snow_mountain)-MIN(snow_mountain)>10

Upvotes: 2

Related Questions