On Tue, Jul 5, 2011 at 10:42 AM, M. D. <li...@turnkey.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.
