Page 1 of 1

Count records and compare with a field of a table

PostPosted: Tue Oct 19, 2010 4:54 pm
by vegafacundodaniel
Hello,

I need help please :

I need to make a query to count records (grouping by a field, for example "country") and compare them against the value of a field of a table.

Any idea ?

Thanks in advance !

Re: Count records and compare with a field of a table

PostPosted: Tue Oct 19, 2010 6:52 pm
by Akatsukami
Your request is not sufficiently coherent to give any definite answer.

Where will the records come from: a PS data set ("flat file")? an ESDS? a KSDS? an IMS or DB2 data base? (in which case the term "records" is incorrect; it should be "segments" or "rows").

Re: Count records and compare with a field of a table

PostPosted: Tue Oct 19, 2010 6:54 pm
by GuyC
something like these : (all different performance depending on #rows and indexes)?
non-correlated :
select * from
(select ctry,count(*) as cnt from tab1 group by ctry)  A
, tab2 B
where A.ctry = B.ctry and A.cnt <> B.cnt

or
correlated
select * from
tab2 B
join table (select count(*) as cnt from tab1 A1 where A1.ctry = B.ctry )  A on 1=1
where A.cnt <> B.cnt

or
in where subselect (actual count(*) does not show up in result)
select * from
tab2 B
where B.cnt <> (select count(*) as cnt from tab1 A1 where A1.ctry = B.ctry )