Re: Debit / Credit SQL Output

Dan, I see two solutions to your puzzle (besides using temporary tables). I) Create two simple stored procedures, e.g. called deb and crd. They both accept an amount and a debit/credit flag (transaction type). The deb procedure returns the value if it is a debit type, otherwise it returns zero (or null). Similarly, crd returns only credit values. I leave it to you to create the procedures but your select statement would ultimately look something like: select acctnum,sum(deb(value,trantype)),sum(crd(value,trantype)) from transactions group by acctnum II) The following select statement should work all by itself: select distinct acctnum, (select sum(value) from transactions where acctnum=tr.acctnum and trantype="Debit"), (select sum(value) from transactions where acctnum=tr.acctnum and trantype="Credit") from transactions tr The first solution is probably better in the long run as you run into the problem over and over again. It also enables you to report ungrouped detail data in debit/credit columns, like: select acctnum,deb(value,trantype),crd(value,trantype) from transactions Hope this helps, ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl frantz@centrum.is http://www.rl.is/~john/pow4gl.html In article <33D25A3E.F9FBE94F@stinky.quikrun.com>, Dan Armstrong <dana@stinky.quikrun.com> 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? > > Thanks a lot, Dan. -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet