Re: Debit / Credit SQL Output
At 10:54 AM 7/28/97 CDT, 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.
}
This may not be the easiest solution, but it seems to work...
SELECT acctnum, SUM(value) cr, 0 deb
FROM trantable
WHERE trantype = "Credit"
GROUP BY 1
UNION ALL
SELECT acctnum, 0 cr, SUM(value) deb
FROM trantable
WHERE trantype = "Debit"
GROUP BY 1
INTO TEMP junk WITH NO LOG;
SELECT acctnum, SUM(cr), SUM(deb)
FROM junk
GROUP BY 1;
You may want to drop "junk" after you're done.
Regards,
Eliott.