Re: Multiple counts when joining files

This works

Create table rob/gdihqcomp as(
SELECT cast(DIOBLI as char(10)) as Library,
cast(SUM(HQ.DIOBSZ) as dec(13)) AS HQSIZE,
cast((select SUM(HQ2.DIOBSZ)
from rob/gdihq2obj hq2
where hq2.diobli=hq.diobli) as dec(13)) as hq2size,
cast(COUNT(*) as dec(7)) AS HQNBROBJ,
cast((select count(*)
from rob/gdihq2obj hq2
where hq2.diobli=hq.diobli) as dec(7)) as hq2nbrobj,
cast(MAX(HQ.DICDAT) as char(6) CCSID 37) AS CHGDATE
FROM ROB/GDIHQOBJ hq
WHERE hq.DIOBLI<>' ' AND hq.DIOBLI <>'QDOC'
GROUP BY hq.DIOBLI
ORDER BY hq.DIOBLI) with data


Rob Berendt