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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
