On Sunday, March 15, 2015, Robert James <srobertja...@gmail.com> 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.
