Version 9.2.4 On 3/15/15, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote: > On Sunday, March 15, 2015, Robert James <srobertjames@xxxxxxxxx> wrote: > >> How do I calculate the sum of a field filtered by multiple windows >> defined by another field? >> >> I have table event with fields event_date, num_events, site_id. I can >> easily use aggregate SQL to do SELECT SUM(num_events) GROUP BY >> site_id. >> >> But I also have another table site with fields site_id, target_date. >> I'd like to do a JOIN, showing the SUM of num_events within 60 days of >> the target_date, 90 days, 120 days, etc. I thought this could easily >> be done using a WHERE clause in the aggregate SQL. However, this is >> complicated by two challenges: >> >> 1. The target_date is not fixed, but varies for each site_id >> >> 2. I'd like multiple date ranges to be outputed in the same table; so >> I can't do a simple WHERE to exclude records falling outside the range >> from the event table >> >> One workaround I've thought of is to simply make several queries, one >> for each date range, using a different WHERE clause for each, and then >> use a view to paste them together. Is there a simpler, better, or more >> elegant way to achieve my goals? >> >> > I suggest you create and post a sample query, ideally using WITH & VALUES > for sample data, that gets you the answer using UNION. You will then have > something to compare against and others can know exactly what you want > instead of trying to figure it out from your limited description. > > What version does the solution need to work for? > > David J. > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
