Re: sql question
On Tue, 14 Dec 1999, Jonathan Leffler wrote:
>"M.J. Holley" wrote:
>> I have table1 with fields acctnum, amt1, ident
>>
>> I need to build either a table or a view which contains rows of
>> acctnum, sum(amt1) where ident = "C", sum(amt1) where ident="R" for each
>> acctnum in table1.
>>
>> The final report is being done in crystal reports (not my doing!) which is
>> why everything has to be pretty clean in one table.
>>
>> How can I write a select to do this? Everything I've tried balks under more
>> than one "where" condition for each part.
>
>Time to get to the entertaining parts of SQL:
-- Tested code!!! --
>SELECT T1a.acctnum,
> (SELECT SUM(amt1)
> FROM Table1 T1b
> WHERE T1b.Acctnum = T1a.Acctnum AND T1b.Ident = "C") AS c_sum,
> (SELECT SUM(amt1)
> FROM Table1 T1c
> WHERE T1c.Acctnum = T1a.Acctnum AND T1c.Ident = "R") AS r_sum
> FROM Table1 T1a
> GROUP BY T1a.acctnum;
This select statement works and produces plausible output. On Solaris 7
running 7.31.UC2 (and SQLCMD 53, and CSDK 2.30.UC1), I created some data
with the SQL statements:
CREATE TEMP TABLE table1 (acctnum INTEGER, ident CHAR(1), amt1 DECIMAL);
INSERT INTO table1 VALUES(123, "R", 222.22);
INSERT INTO table1 VALUES(123, "R", 111.22);
INSERT INTO table1 VALUES(123, "C", 333.22);
INSERT INTO table1 VALUES(123, "R", 444.22);
INSERT INTO table1 VALUES(123, "C", 555.22);
INSERT INTO table1 VALUES(123, "R", 666.22);
INSERT INTO table1 VALUES(123, "C", 777.22);
INSERT INTO table1 VALUES(223, "R", 999.22);
INSERT INTO table1 VALUES(223, "R", 111.22);
INSERT INTO table1 VALUES(223, "C", 888.22);
INSERT INTO table1 VALUES(223, "R", 444.22);
INSERT INTO table1 VALUES(223, "C", 555.22);
INSERT INTO table1 VALUES(223, "R", 666.22);
INSERT INTO table1 VALUES(223, "C", 777.22);
I ran the query as stated and the results are (reformatted):
acctnum c_sum r_sum
INTEGER DECIMAL(32) DECIMAL(32)
123 1665.66 1443.88
223 2220.66 2220.88
I've not looked at the query plan for efficiency.
--
Yours,
Jonathan Leffler (jonathan.leffler@informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v0.62 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"