pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgroup by with sum and sum till max date
2+ messages / 2 participants
[nested] [flat]
* group by with sum and sum till max date
@ 2011-07-05 17:42 M. D. <lists@turnkey.bz>
2011-07-06 07:04 ` Re: group by with sum and sum till max date Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 2+ messages in thread
From: M. D. @ 2011-07-05 17:42 UTC (permalink / raw)
To: pgsql-sql
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"
Is this possible?
Thanks,
Mark
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: group by with sum and sum till max date
2011-07-05 17:42 group by with sum and sum till max date M. D. <lists@turnkey.bz>
@ 2011-07-06 07:04 ` Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 2+ messages in thread
From: Samuel Gendler @ 2011-07-06 07:04 UTC (permalink / raw)
To: M. D. <lists@turnkey.bz>; +Cc: pgsql-sql
On Tue, Jul 5, 2011 at 10:42 AM, M. D. <lists@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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-07-06 07:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-05 17:42 group by with sum and sum till max date M. D. <lists@turnkey.bz>
2011-07-06 07:04 ` Samuel Gendler <sgendler@ideasculptor.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox