Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id B042419F7A76 for ; Wed, 30 Nov 2011 21:03:49 -0400 (AST) Received: from dime159.dizinc.com ([66.7.216.77]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RVv3y-00056P-7Z for pgsql-sql@postgresql.org; Thu, 01 Dec 2011 01:03:48 +0000 Received: from [200.32.232.149] (port=53769 helo=[192.168.54.128]) by dime159.dizinc.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1RVv3l-0000Xo-3U for pgsql-sql@postgresql.org; Wed, 30 Nov 2011 20:03:33 -0500 Message-ID: <4ED6D261.4060202@turnkey.bz> Date: Wed, 30 Nov 2011 19:03:29 -0600 From: "M. D." User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:8.0) Gecko/20111124 Thunderbird/8.0 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: running totals with end of month line Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - dime159.dizinc.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - turnkey.bz X-Source: X-Source-Args: X-Source-Dir: X-Pg-Spam-Score: 2.9 (++) X-Archive-Number: 201111/87 X-Sequence-Number: 36060 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