Re: running totals with end of month line

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