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