I've got several tables with weather data (temperatures in this case) structured thusly:
region, year, january, february, march, april, etc....
and they all work fine. I've used them to calculate views and tables with similar structure, and that all works fine. No NULLs, data types are consistent, values are accurate. But then I run the following on a certain table
SELECT region,
(CASE WHEN year < 1931 THEN ROUND(AVG(january), 2) END) -
(CASE WHEN year > 1990 THEN ROUND(AVG(january), 2) END) as delta_variance
FROM temps_variance
GROUP BY region;
If I run the cases separately, the first case returns a few NULLs, mostly values. The second case returns mostly NULLs, a few values. When I run the whole statement, the subtraction lines up to just give a column full of NULLs.
The data is all there and all consistent, I can't find any blanks or missing rows or numeric anomalies. The table has every region with every year and valid values in each cell. Putting other years in the CASEs just returns varying proportions of values and NULLs.
I ran similar statements on another table
SELECT ncdc_fips, year,
(CASE WHEN year < 1931 THEN ROUND(AVG(january+february+march+april+may+june+july+august+september+october+november+december)/12, 2) END) AS past_min,
(CASE WHEN year > 1990 THEN ROUND(AVG(january+february+march+april+may+june+july+august+september+october+november+december)/12, 2) END) AS recent_min
FROM temps_min
GROUP BY ncdc_fips;
and got incorrect values for the first case, and NULLs for the second case. Some experimentation showed that it would return NULLs for every year except 1895, the first year of data. And it averaged the data from all years to get the values for that. Total mess.
So is it:
1 - I missed a comma or something,
2 - I don't understand how CASE statements in a SELECT clause work, or
3 - a MySQL quirk?
