running totals with end of month line

From: "M(dot) D(dot)" <lists(at)turnkey(dot)bz>
To: pgsql-sql(at)postgresql(dot)org
Subject: running totals with end of month line
Date: 2011-12-01 01:03:29
Message-ID: 4ED6D261.4060202@turnkey.bz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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">&gt;</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>

Attachment Content-Type Size
unknown_filename text/html 2.1 KB

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David Johnston 2011-12-01 01:12:22 Re: running totals with end of month line
Previous Message Muiz 2011-11-29 02:00:35 plpgsql: how to get the exception's detail information?