Message-ID: <435909FD.5040904@comcast.net>
Date: Fri, 21 Oct 2005 11:32:13 -0400
From: Jerry Blumenthal <jerblum4@comcast.net>
Reply-To: jerry@blumenthalsoftware.com
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Aggregate versus lineitem report
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

How do you get a report that lists items and then produces a sum at the 
end.  Like this:

name     date   amount1   amount2
name     date   amount1   amount2
name     date   amount1   amount2

totals             sum(amount1)   sum(amount2)

The sql command I am using now is

  SELECT SUM(Tcode.T_amount) Fees,
         SUM(Tcharges.T_discount) Discount,
         Tcode.T_prov Provider
    FROM "C:\zz\Tcode.DAT" Tcode
         INNER JOIN "C:\zz\Tcharges.DAT" Tcharges
                 ON Tcode.T_acctnum = Tcharges.T_acctnum
                AND Tcode.T_link = Tcharges.T_link
   WHERE (((Tcode.T_posted BETWEEN "01/01/2004" AND "01/31/2004")
     AND (Tcode.T_tcode < 30000)))
GROUP BY Provider

And that shows a list of amounts, for each provider, but I want to be 
able to show all the line items that added up to those totals.  (I didnt 
include the group by provider issue in my question because that is nice 
but not necessary- I can always just do one provider at a time with a 
clause like "WHERE provider = 1")

TIA
Jerry