running totals with end of month line - Mailing list pgsql-sql

From M. D.
Subject running totals with end of month line
Date
Msg-id 4ED6D261.4060202@turnkey.bz
Whole thread Raw
Responses Re: running totals with end of month line
List pgsql-sql
Hi everyone,<br /><br /> I would like to create a query that does a running total for each account, but I also want to
showa 'phantom' row that gives the end of month with the last day of the month as the transaction date.<br /><br />
Here'sa 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><spanclass="syntax18">(</span>o.order_total<span class="syntax18">)</span>,<span
class="syntax5">0</span><spanclass="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>

pgsql-sql by date:

Previous
From: Belinda Cussen
Date:
Subject: Re: Does anyone know of any issues around ARRAY UNNEST
Next
From: David Johnston
Date:
Subject: Re: running totals with end of month line