<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">You were right Regina. I did some testing and the subselects were indeed returning averages for the entire weather table. <DIV><BR class="khtml-block-placeholder"></DIV><DIV>I also pulled all the -999 values out and replaced them with NULLs. The way it should have been in the first place. </DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Here is my latest crack at your suggestions (including getting rid of the double subselect), However I'm getting syntax error at the FROM weather line at the end that I just can't find. Do you see it? </DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>SELECT CASE</DIV><DIV>WHEN w.station_id = site_near.station_id THEN w.obs_id</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>ELSE s.obs_id</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>END AS obs_id, site_near.station_id, site_near.longname, w.year, w.doy</DIV><DIV>FROM site_near</DIV><DIV>INNER JOIN solar s</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>ON (site_near.ref_solar_station_id = s.station_id AND site_near.obs_year = s.year)</DIV><DIV>INNER JOIN weather w</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>ON (site_near.ref_weather_station_id = w.station_id AND site_near.obs_year = w.year AND s.date = w.date)</DIV><DIV>INNER JOIN (SELECT month, station_id,</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>round(avg(precip)::numeric, 2) AS avgprecip,</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>round(avg(tmin)::numeric, 2) AS avgtmin,</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>round(avg(tmax)::numeric, 2) AS avgtmax,</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>round(avg(par)::numeric, 2) AS avgpar,</DIV><DIV>FROM weather</DIV><DIV>GROUP BY month, station_id) AS avgclim</DIV><DIV>WHERE w.station_id = 219101;</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV><BR><DIV><DIV>On Aug 14, 2007, at 3:44 PM, Obe, Regina wrote:</DIV><BR class="Apple-interchange-newline"><BLOCKQUOTE type="cite"> <DIV dir="ltr" align="left"><SPAN class="072472720-14082007"><FONT face="Arial" color="#0000ff" size="2">Yes I think you did or I missed something. Your subselects are going to return the average for the whole weather and solar tables (not for just that reference site) which doesn't quite seem like what you want if you are trying to put realistic placeholders for missing data in a site. </FONT></SPAN></DIV> <DIV dir="ltr" align="left"><SPAN class="072472720-14082007"><FONT face="Arial" color="#0000ff" size="2"></FONT></SPAN> </DIV> <DIV dir="ltr" align="left"><SPAN class="072472720-14082007"><FONT face="Arial" color="#0000ff" size="2">Also I don't think you need to do 2 subselects for weather - I think you can combine into a single one with something like below</FONT></SPAN></DIV> <DIV dir="ltr" align="left"><SPAN class="072472720-14082007"><FONT face="Arial" color="#0000ff" size="2"></FONT></SPAN> </DIV> <DIV dir="ltr" align="left"><SPAN class="072472720-14082007"><FONT face="Arial" color="#0000ff" size="2"><FONT face="Times New Roman" color="#000000" size="3">(SELECT MONTH, station_id,</FONT> <DIV> round(avg(<SPAN class="072472720-14082007">CASE WHEN </SPAN>precip<SPAN class="072472720-14082007"> != -999 THEN precip ELSE NULL END</SPAN>)::numeric, 2) AS avg<SPAN class="072472720-14082007">precip, </SPAN></DIV> <DIV><SPAN class="072472720-14082007"> round(avg(CASE WHEN tmax != -999 THEN tmax ELSE NULL END)::numeric, 2) AS avgtmax,</SPAN></DIV> <DIV> FROM weather</DIV> <DIV> GROUP BY MONTH<SPAN class="072472720-14082007">, station_id) As wmonthsummer</SPAN></DIV> <DIV><SPAN class="072472720-14082007"></SPAN> </DIV> <DIV><SPAN class="072472720-14082007">Actually if you had put in NULLS for precip amd tmax instead of -999, you would be better off since those fall out of the equation quite nicely since aggregates completely ignore null.</SPAN></DIV> <DIV><SPAN class="072472720-14082007"></SPAN> </DIV> <DIV><SPAN class="072472720-14082007">And then you could blissfully do</SPAN></DIV> <DIV><SPAN class="072472720-14082007"></SPAN> </DIV> <DIV><SPAN class="072472720-14082007"><SPAN class="072472720-14082007"><FONT face="Arial" color="#0000ff" size="2"><FONT face="Times New Roman" color="#000000" size="3">(SELECT MONTH, station_id,</FONT> <DIV> round(avg(precip)::numeric, 2) AS avg<SPAN class="072472720-14082007">precip, </SPAN></DIV> <DIV><SPAN class="072472720-14082007"> round(avg(tmax)::numeric, 2) AS avgtmax,</SPAN></DIV> <DIV> FROM weather</DIV> <DIV> GROUP BY MONTH<SPAN class="072472720-14082007">, station_id) As wmonthsummer</SPAN></DIV> <DIV><SPAN class="072472720-14082007"></SPAN> </DIV> <DIV><SPAN class="072472720-14082007">Hope that helps,</SPAN></DIV> <DIV><SPAN class="072472720-14082007">Regina</SPAN></DIV></FONT></SPAN></SPAN></DIV></FONT></SPAN></DIV><BR> <DIV class="OutlookMessageHeader" lang="en-us" dir="ltr" align="left"> <HR tabindex="-1"> <FONT face="Tahoma" size="2"><B>From:</B> postgis-users-bounces@postgis.refractions.net [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] <B>On Behalf Of </B>Kirk Wythers<BR><B>Sent:</B> Tuesday, August 14, 2007 4:25 PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] monthly climate query<BR></FONT><BR></DIV> <DIV></DIV>If I follow your point Regina (as far as the working daily query goes), I am returning data for a particular site based on the last line... WHERE w.station_id = some_valid_number. <DIV><BR class="khtml-block-placeholder"></DIV> <DIV>Did I miss something? <DIV><BR> <DIV> <DIV>On Aug 14, 2007, at 3:13 PM, Obe, Regina wrote:</DIV><BR class="Apple-interchange-newline"> <BLOCKQUOTE type="cite"> <DIV dir="ltr" align="left"><SPAN class="198020920-14082007"><FONT face="Arial" color="#0000ff" size="2">Wouldn't you want to return the average for month for that particular site? None of your month subselects seem to group by station id. Would seem to me logically you should add station_id to each of your subselects, group by station_id as well as month and then your ON clause would be month and station_id.</FONT></SPAN></DIV> <DIV dir="ltr" align="left"><SPAN class="198020920-14082007"><FONT face="Arial" color="#0000ff" size="2"></FONT></SPAN> </DIV> <DIV dir="ltr" align="left"><SPAN class="198020920-14082007"><FONT face="Arial" color="#0000ff" size="2">Hope that helps,</FONT></SPAN></DIV> <DIV dir="ltr" align="left"><SPAN class="198020920-14082007"><FONT face="Arial" color="#0000ff" size="2">Regina</FONT></SPAN></DIV> <DIV dir="ltr" align="left"><BR></DIV> <DIV class="OutlookMessageHeader" lang="en-us" dir="ltr" align="left"> <HR tabindex="-1"> <FONT face="Tahoma" size="2"><B>From:</B> <A href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A> [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] <B>On Behalf Of </B>Kirk Wythers<BR><B>Sent:</B> Tuesday, August 14, 2007 12:15 PM<BR><B>To:</B> Discussion PostGIS Users<BR><B>Subject:</B> [postgis-users] monthly climate query<BR></FONT><BR></DIV> <DIV></DIV> <DIV style="MARGIN: 0px"><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="FONT-SIZE: 11px">I need some help with rewriting a query. I have a query that dumps daily climate data, filling in missing data with monthly averages (one line per day). </SPAN></FONT><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="FONT-SIZE: 11px"></SPAN></FONT></DIV> <DIV style="MARGIN: 0px"><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="FONT-SIZE: 11px"><BR class="khtml-block-placeholder"></SPAN></FONT></DIV> <DIV style="MARGIN: 0px"><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="FONT-SIZE: 11px">I want to output monthly averages (one line per month). I am having a hard time wrapping my head around this. Particularly how to deal with the doy column (day of year). I have tried several approaches and my forehead is starting to get my keyboard bloody. </SPAN></FONT></DIV> <DIV style="MARGIN: 0px"><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="FONT-SIZE: 11px"><BR class="khtml-block-placeholder"></SPAN></FONT></DIV> <DIV style="MARGIN: 0px"><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="FONT-SIZE: 11px">Here is the daily query:</SPAN></FONT></DIV> <DIV style="MARGIN: 0px"><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="FONT-SIZE: 11px"><BR class="khtml-block-placeholder"></SPAN></FONT></DIV> <DIV>SELECT CASE</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>WHEN w.station_id = site_near.station_id THEN w.obs_id</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>ELSE s.obs_id</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>END AS obs_id,</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>site_near.station_id,</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>site_near.longname,</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>w.year,</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>w.doy,</DIV> <DIV>--replace missing values (-999) with the monthly average</DIV> <DIV> CASE w.tmax</DIV> <DIV> WHEN -999 THEN avgtmax.avg</DIV> <DIV> ELSE w.tmax</DIV> <DIV> END,</DIV> <DIV>CASE w.tmin</DIV> <DIV> WHEN -999 THEN avgtmin.avg</DIV> <DIV> ELSE w.tmin</DIV> <DIV> END,</DIV> <DIV>CASE s.par</DIV> <DIV> WHEN -999 THEN avgpar.avg</DIV> <DIV> ELSE s.par</DIV> <DIV> END,</DIV> <DIV>CASE w.precip</DIV> <DIV> WHEN -999 THEN avgprecip.avg</DIV> <DIV> ELSE w.precip</DIV> <DIV> END</DIV> <DIV>FROM site_near</DIV> <DIV> INNER JOIN solar s</DIV> <DIV> ON (site_near.ref_solar_station_id = s.station_id</DIV> <DIV> AND site_near.obs_year = s.year)</DIV> <DIV> INNER JOIN weather w</DIV> <DIV> ON (site_near.ref_weather_station_id = w.station_id</DIV> <DIV> AND site_near.obs_year = w.year</DIV> <DIV> AND s.date = w.date)</DIV> <DIV> INNER JOIN (SELECT MONTH,</DIV> <DIV> round(avg(tmax)::numeric, 2) AS avg</DIV> <DIV> FROM weather</DIV> <DIV> WHERE tmax != -999</DIV> <DIV> GROUP BY MONTH) AS avgtmax</DIV> <DIV> ON (w.month = avgtmax.month)</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>INNER JOIN (SELECT MONTH,</DIV> <DIV> round(avg(tmin)::numeric, 2) AS avg</DIV> <DIV> FROM weather</DIV> <DIV> WHERE tmin != -999</DIV> <DIV> GROUP BY MONTH) AS avgtmin</DIV> <DIV> ON (w.month = avgtmin.month)</DIV> <DIV> INNER JOIN (SELECT MONTH,</DIV> <DIV> round(avg(par)::numeric, 2) AS avg</DIV> <DIV> FROM solar</DIV> <DIV> WHERE par != -999</DIV> <DIV> GROUP BY MONTH) AS avgpar</DIV> <DIV> ON (s.month = avgpar.month)</DIV> <DIV><SPAN class="Apple-tab-span" style="WHITE-SPACE: pre"></SPAN>INNER JOIN (SELECT MONTH,</DIV> <DIV> round(avg(precip)::numeric, 2) AS avg</DIV> <DIV> FROM weather</DIV> <DIV> WHERE precip != -999</DIV> <DIV> GROUP BY MONTH) AS avgprecip</DIV> <DIV> ON (w.month = avgprecip.month)</DIV> <DIV>--select station to output climate data by id number</DIV> <DIV>WHERE w.station_id = 219101</DIV> <DIV><BR class="khtml-block-placeholder"></DIV> <HR size="1"> <DIV><BR class="khtml-block-placeholder"></DIV><P><STRONG>The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. </STRONG></P> <DIV style="MARGIN: 0px">_______________________________________________</DIV> <DIV style="MARGIN: 0px">postgis-users mailing list</DIV> <DIV style="MARGIN: 0px"><A href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV> <DIV style="MARGIN: 0px"><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV></BLOCKQUOTE></DIV><BR></DIV></DIV><DIV><BR class="khtml-block-placeholder"></DIV><HR size="1"><DIV><BR class="khtml-block-placeholder"></DIV><P><STRONG> The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. </STRONG></P><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">postgis-users mailing list</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV> </BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>