Обсуждение: How do I calculate the sum of a field filtered by multiple windows defined by another field?

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?

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?

Version 9.2.4

On 3/15/15, David G. Johnston <david.g.johnston@gmail.com> wrote:
> 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.
>