Re: Debit / Credit SQL Output

Dan Armstrong wrote: > > I can't believe I am hung up on this one, but I just can't figure it > out: > > I am writing an A/R accounting system for a customer. I have an AR > Transactions table which basically contains an account number, a value > (money) and whether it is a debit or credit. > > My problem is with the output. Any accounting report has the debits > and > > credits listed in two columns. What I need is to group by account > number, and put the sum(debits) and sum(credits) into two separate > columns. Like this: > > ACCTNUM CREDITS DEBITS > > 12000 $100.00 $00.00 > 13000 $200.00 $10.00 > etc. > > The table I am trying to run a query against looks like this: > > ACCTNUM VALUE TRANTYPE > > 12000 $50.00 Credit > 12000 $50.00 Credit > 13000 $100.00 Credit > 13000 $100.00 Credit > 13000 $10.00 Debit > > That nasty Ansi compliant GROUP BY clause has got me hog tied here...I > have tried using the CASE statement, in which I get duplicate account > numbers in the output, I have even tried a self-join which yields > completely whacko results...This is so simple, does anybody have any > thoughts on the SQL Query that could yield the correct results? Have a look at the WHERE clause. Something like: AFTER GROUP OF acctnum PRINT COLUMN x, acctnum, COLUMN y, GROUP SUM(value) WHERE trantype = "Credit", COLUMN z, GROUP SUM(value) WHERE trantype = "Debit" I haven't checked TFM, but I'll leave that to you. :-) Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+