sql question
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. Thanks...MJ
M.J. Holley <datashark@fastlane.net> schrieb in im Newsbeitrag: E2E7B1C715486CF1.3B8BA763D49A4F2F.9E929BCEE36A1933@lp.airnews.net...
> 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.
>
> Thanks...MJ
>
>
Try the following: It is a three step solution, not very smart but it works.
select acctnum, sum(amt1) Csum
from table1
where ident = "C"
into temp t1;
select acctnum, sum(amt1) Rsum
from table1
where ident = "R"
into temp t2;
select t1.acctnum,t1.Csum , t2.Rsum
from t1, outer t2
where t1.acctnum = t2.acctnum
union
select t2.acctnum,t1.Csum, t2.Rsum
from outer t1, t2
where t1.acctnum = t2.acctnum
into temp t3;
update t3 set Csum = 0 where Csum is null;
update t3 set Rsum = 0 where Rsum is null;
select * from t3;
HTH,
Reinhard
What about
select acctnum, sum(amt1) as csum, 0 as rsum
from table1
where ident = "C"
group by 1
union
select acctnum, 0 as csum, sum(amt1) as rsum
from table1
where ident = "R"
group by 1
into temp t1;
select acctnum, sum(csum), sum(rsum)
from t1
group by 1
"M.J. Holley" <datashark@fastlane.net> wrote in message
news:E2E7B1C715486CF1.3B8BA763D49A4F2F.9E929BCEE36A1933@lp.airnews.net...
> 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.
>
> Thanks...MJ
>
>
--
----
Carlos Costa e Silva <ccs@minimal.pt>
Minimal Lda
Lisboa
Portugal
"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:
-- Untested 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;
I'm not certain that you can do those correlated sub-queries in the
SELECT list; you can write some sub-queries like that, though. Yeah,
it blew my mind when I first came across it, too. Blame SQL-92. Roll
on SQL-99.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
This is what they wrote the case expression for:
select acctnum,
sum(case when ident = "C" then amt1 else 0 end) as c_sum,sum(case when ident = "R" then amt1 else 0 end) as r_sum
from your_table
group by acctnum;
This requires a single SQL and no subselects.
Jay Buckler
M.J. Holley <datashark@fastlane.net> wrote in message
news:E2E7B1C715486CF1.3B8BA763D49A4F2F.9E929BCEE36A1933@lp.airnews.net...
> 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.
>
> Thanks...MJ
>
>