From: | Robert James <srobertjames(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How do I calculate the sum of a field filtered by multiple windows defined by another field? |
Date: | 2015-03-16 04:05:48 |
Message-ID: | CAGYyBgj+mkqTozg0-=e0DA9B96BZGRkt4U2JdeHF0Q8chHDABw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Version 9.2.4
On 3/15/15, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Sunday, March 15, 2015, Robert James <srobertjames(at)gmail(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ruth Melendo | 2015-03-16 13:48:40 | pgpool + BDR, is it possible? |
Previous Message | Tom Lane | 2015-03-15 16:51:43 | Re: Preserving formatting and comments in a VIEW definition |