RE: Debit / Credit SQL Output

Sujit Pal <spal@scotch.den.csci.csc.com> wrote: }Try as I might I could not do this with a single query. Maybe it can be done? It can be done with a 'single query', but you don't need to look in the manuals for an explanation of the syntax -- it isn't there. This sort of thing has been possible, as far as I recall, since the 5.0x servers were released. However, since it isn't documented, you might quite legitimately decide not to use it. With the exception of the fact that SQL returns NULL rather than 0 as the sum of an empty set (see C J Date for more rantings on that subject; I agree with him almost completely, FWIW), this query does what you want, almost directly (I used a table accts for the data, rather than the table AR used by Sujit): SELECT acctnum, (SELECT SUM(value) FROM accts A2 WHERE A1.acctnum = A2.Acctnum AND A2.trantype = 'C' GROUP BY acctnum ) AS credits, (SELECT SUM(value) FROM accts A3 WHERE A1.acctnum = A3.Acctnum AND A3.trantype = 'D' GROUP BY acctnum ) AS debits FROM accts A1 GROUP BY acctnum; acctnum credits debits 12000 100.00 13000 200.00 10.00 I made no mention of the term efficiency -- I think the versions using UNION are more likely to be efficient on normal tables -- but I've not looked at the query plans and this may work OK. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path. }From: Sujit Pal <spal@scotch.den.csci.csc.com> }Date: Mon, 28 Jul 1997 11:16:29 -0600 }X-Informix-List-Id: <list.15652> } }[...] I have a solution for you using }UNION and TEMP tables. Here it is: } }CREATE TABLE ar ( /* Table definition */ } acctnum INTEGER, } value DECIMAL(5,2), } trantype CHAR(1)); }LOAD FROM ar.dat INSERT INTO ar; /* Load table w/your data */ }SELECT acctnum, value credit, 0 debit /* Heres the query */ } FROM ar } WHERE trantype = "C" }UNION ALL }SELECT acctnum, 0 credit, value debit } FROM ar } WHERE trantype = "D" }INTO TEMP t_ar; }SELECT acctnum, sum(credit) credit, sum(debit) debit } FROM t_ar } GROUP BY 1 } ORDER BY 1; [...] }From: Dan Armstrong[SMTP:dana@stinky.quikrun.com] }Sent: Sunday, July 20, 1997 12:34 PM } }I am writing an A/R accounting system [with a] Transactions table which }basically contains an account number, a value (money) and whether it is a }debit or credit. } }[...] 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 }13000 $100.00 Credit }13000 $100.00 Credit }13000 $10.00 Debit [...]