Re: group by with sum and sum till max date

On Tue, Jul 5, 2011 at 10:42 AM, M. D. <lists(at)turnkey(dot)bz> wrote:

> This is a little hard to explain, and I'm not sure if it's possible, but
> here goes.
>
> This is my query:
> select year, month,
> (select number from account where account.account_id =
> view_account_change.account_**id) as number,
> (select name from account where account.account_id =
> view_account_change.account_**id) as account,
> sum(amount) as amount
> from view_account_change
> where view_account_change.change_**date >= '2010-01-01'
> group by year,month, number, account
> order by year,month, number, account
>
> I want to make an exception for the sum so that if the account number is
> less than 4000, I want a sum of all transactions until the last date of the
> group by.
>
> the query for that would be:
> Select sum(amount) from view_account_change where change_date > "max date
> in the group"
>

I think you are looking for a window function, but I'm not sure about using
a value computed over a window in the where clause. You may have to do
something somewhat complicated with a subquery, but you can definitely
compute 'max date in the group' via a window function:

http://www.postgresql.org/docs/9.0/static/tutorial-window.html
http://www.postgresql.org/docs/9.0/static/functions-window.html
http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

You'll like have to split it into a UNION of 2 queries, one for account
numbers lower than 4000 and the other for the rest.

Perhaps select max date in the group in a subquery which you then join to in
an outer query. That should be enough to start experimenting with, anyway.