From: David Johnston <polobo@yahoo.com>
To: M. D. <lists@turnkey.bz>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: running totals with end of month line
Date: Wed, 30 Nov 2011 20:12:22 -0500
Message-ID: <E71E635E-4A47-46BF-8899-78D1837D6FBA@yahoo.com> (raw)
In-Reply-To: <4ED6D261.4060202@turnkey.bz>
References: <4ED6D261.4060202@turnkey.bz>
On Nov 30, 2011, at 20:03, "M. D." <lists@turnkey.bz> wrote:
> Hi everyone,
>
> I would like to create a query that does a running total for each account, but I also want to show a 'phantom' row that gives the end of month with the last day of the month as the transaction date.
>
> Here's a sample query:
> SELECT n.customer_id, n.order_id, n.order_total,
> COALESCE(SUM(o.order_total),0) As past_order_total
> FROM orders AS n LEFT JOIN orders AS o
> ON (o.customer_id = n.customer_id
> AND n.order_datetime > o.order_datetime)
> GROUP BY n.customer_id, n.order_datetime, n.order_id, n.order_total
> ORDER BY n.customer_id, n.order_datetime, n.order_id;
>
> --- taken from http://bit.ly/speZzs
>
> Is there a way to have that 'phantom' row for each account? I want to result to be ordered by customer_id, account_type.
>
> More details:
> In my situation, I have Customers and Grain types.
> I want to generate a result that will show Customer, Grain Type, Daily Avg Bal, Charge Rate, discount, Charge.
> Maybe it's not really possible. I see it a bit hard group it properly, showing only single row per customer per grain.
>
>
> Many thanks,
> Mark
You need to write the totaling query and then UNION ALL it with the detail query. You probably will want to use WINDOW aggregates as well - assuming you are on 8.4 or better.
David J.=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, lists@turnkey.bz
Subject: Re: running totals with end of month line
In-Reply-To: <E71E635E-4A47-46BF-8899-78D1837D6FBA@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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