FW: Debit / Credit SQL Output

} }This is an interesting problem. I would try looking at using a union }statement for your select statement. }select acctnum,value,0.00 from trans_table where transtype = "CREDIT" }union }select acctnum,0.00,value from trans_table where transtype = "DEBIT" } }I don't know what type of reporting tool you are using to write your }report but whatever you are using should allow you to group by field1 }and sum by fields 2 and 3. } }this should provide you the results you are looking for. I have done }this type of reports before and found this to be the easiest way to do }it..... } }hope this helps.... } }Russ... } } }>-----Original Message----- }>From: Dan Armstrong [SMTP:dana@stinky.quikrun.com] }>Sent: Sunday, July 20, 1997 1:35 PM }>To: informix-list@rmy.emory.edu }>Subject: Debit / Credit SQL Output }> }>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. }> }> }> }> }