MySQL and average of multiple columns + indivudual

问题:

I have a following query:

SELECT `station_id`, AVG(`jan`) AS avg_jan, AVG(`feb`) AS avg_feb, ... , 
AVG(`dec`) AS avg_dec 
FROM `climate_data`

WHERE `element_name` = "Temp_mean_mly" 
AND `jan` <> -999999 
AND `feb` <> -999999
AND ... 
AND `dec` <> -999999 
GROUP BY station_id 

I need it to return average of values different than -999999 for each of the columns individually! The current query eliminates all rows with any of columns having value -999999 which is not correct.

Thanks

回答1:

You can use NULLIF like so:

SELECT 
    station_id, 
    AVG(NULLIF(jan, -99999)) AS avg_jan, 
    AVG(NULLIF(feb, -99999)) AS avg_feb,
    ...
FROM climate_data
WHERE element_name = 'Temp_mean_mly'
GROUP BY station_id 

This works because aggregate functions skip NULL values.