pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrunning totals with end of month line
2+ messages / 2 participants
[nested] [flat]
* running totals with end of month line
@ 2011-12-01 01:03 M. D. <lists@turnkey.bz>
2011-12-01 01:12 ` Re: running totals with end of month line David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: M. D. @ 2011-12-01 01:03 UTC (permalink / raw)
To: pgsql-sql
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi everyone,<br>
<br>
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.<br>
<br>
Here's a sample query:<br>
<pre><code><span class="syntax0"><span class="syntax8">SELECT</span> n.customer_id, n.order_id, n.order_total,
<span class="syntax9">COALESCE</span><span class="syntax18">(</span><span class="syntax9">SUM</span><span class="syntax18">(</span>o.order_total<span class="syntax18">)</span>,<span class="syntax5">0</span><span class="syntax18">)</span> <span class="syntax8">As</span> past_order_total
<span class="syntax8">FROM</span> orders <span class="syntax8">AS</span> n <span class="syntax9">LEFT</span> <span class="syntax8">JOIN</span> orders <span class="syntax8">AS</span> o
<span class="syntax8">ON</span> <span class="syntax18">(</span>o.customer_id <span class="syntax18">=</span> n.customer_id
<span class="syntax8">AND</span> n.order_datetime <span class="syntax18">></span> o.order_datetime<span class="syntax18">)</span>
<span class="syntax8">GROUP</span> <span class="syntax8">BY</span> n.customer_id, n.order_datetime, n.order_id, n.order_total
<span class="syntax8">ORDER</span> <span class="syntax8">BY</span> n.customer_id, n.order_datetime, n.order_id;
--- taken from <a class="moz-txt-link-freetext" href="http://bit.ly/speZzs">http://bit.ly/speZzs</a;
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
</span></code></pre>
</body>
</html>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: running totals with end of month line
2011-12-01 01:03 running totals with end of month line M. D. <lists@turnkey.bz>
@ 2011-12-01 01:12 ` David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: David Johnston @ 2011-12-01 01:12 UTC (permalink / raw)
To: M. D. <lists@turnkey.bz>; +Cc: pgsql-sql
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.=
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-12-01 01:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-01 01:03 running totals with end of month line M. D. <lists@turnkey.bz>
2011-12-01 01:12 ` David Johnston <polobo@yahoo.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