Select statement to compare values within same field

DCWORK_GIS

I need help creating a select statement for an attribute table of temperature data. The table has fields for Station_Name, Date and Max_Temp and I would like to select only those records where the temperature difference between weather stations was 20 degrees or more in the same day. I'm stumped on how to compare the 18 Max_Temp values in any single day for each of 365 Dates.

JoshuaBixby

I don't believe the query you provided is giving the answer you expect.  As written, you are using the averages over the entire data set, not just one day, which is what I thought you were wanting.  Try the following, let me know if it works for you (may not be formatted completely correctly for MySQL😞

<SPAN class="keyword token">SELECT</SPAN>&nbsp; dt<SPAN class="punctuation token">.</SPAN>ObjectID <SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt<SPAN class="punctuation token">.</SPAN>Date <SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt<SPAN class="punctuation token">.</SPAN>StationName <SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt<SPAN class="punctuation token">.</SPAN>MaxTemp <SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at<SPAN class="punctuation token">.</SPAN>AvgMax
<SPAN class="keyword token">FROM</SPAN>&nbsp;&nbsp;&nbsp; DailyTemps dt
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">INNER</SPAN> <SPAN class="keyword token">JOIN</SPAN> <SPAN class="punctuation token">(</SPAN> <SPAN class="keyword token">SELECT</SPAN> Date <SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="token function">AVG</SPAN><SPAN class="punctuation token">(</SPAN>MaxTemp<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">AS</SPAN> AvgMax
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN>&nbsp;&nbsp; DailyTemps
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">GROUP</SPAN> <SPAN class="keyword token">BY</SPAN> Date
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">AS</SPAN> at <SPAN class="keyword token">ON</SPAN> dt<SPAN class="punctuation token">.</SPAN>Date <SPAN class="operator token">=</SPAN> at<SPAN class="punctuation token">.</SPAN>Date
<SPAN class="keyword token">WHERE</SPAN>&nbsp;&nbsp; ABS<SPAN class="punctuation token">(</SPAN>dt<SPAN class="punctuation token">.</SPAN>MaxTemp <SPAN class="operator token">-</SPAN> at<SPAN class="punctuation token">.</SPAN>AvgMax<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="number token">10</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>