Thread: running totals with end of month line

running totals with end of month line

From
"M. D."
Date:
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>

Re: running totals with end of month line

From
David Johnston
Date:
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.