I need help creating a select statement for an attribute table of temperature data. I'm trying to select only those results where the temperature difference between weather stations was 20 degrees or more in the same day. I'm stumped on how to compare the Max_Temp values across 18 different stations. I have an attributes table with fields for Station_Name, Date and Max_Temp and I would like to select only those results where the difference between Max_Temp >= 20. There are 18 Station_Names and 18 Max_Temps for each of 365 Dates.
I figured it out. I used two subqueries to test for the upper and lower deviations from the average like so:
SELECT `ObjectID`, `Date`, `StationName`, `MaxTemp` FROM `DailyTemps` WHERE `MaxTemp` > (SELECT AVG(`MaxTemp`) FROM `DailyTemps`) +10 OR `MaxTemp` < (SELECT AVG(`MaxTemp`) FROM `DailyTemps`) -10