Re: Multiple counts when joining files

Rob,

My apologies for the late post (yesterday was a national holiday for us in
Venezuela, and I wasn't at work).

I think that your original statement just needed a slight modification:

SELECT
hq.DIOBLI,
SUM(HQ.DIOBSZ) AS HQSIZE,
count(hq.diobnm) AS count_hq,
SUM(HQ2.DIOBSZ) AS HQ2SIZE,
count(hq2.diobnm) AS count_hq2,
MAX(HQ.DICDAT) AS CHANGEDDATE

FROM GDIHQOBJ HQ
LEFT OUTER JOIN GDIHQ2OBJ HQ2 USING (DIOBLI, DIOBNM, DIOBTP)
WHERE hq.DIOBLI<>' ' AND hq.DIOBLI <>'QDOC'
GROUP BY hq.DIOBLI
ORDER BY hq.DIOBLI
;

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Tue, Apr 19, 2011 at 4:32 PM, <rob@xxxxxxxxx> wrote:

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
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: rob@xxxxxxxxx
To: midrange-l@xxxxxxxxxxxx
Date: 04/19/2011 03:59 PM
Subject: Multiple counts when joining files
Sent by: midrange-l-bounces@xxxxxxxxxxxx



I can't seem to get this right:

SELECT DIOBLI, SUM(HQ.DIOBSZ) AS HQSIZE,
COUNT(HQ ) AS HQNBROBJ,
SUM(HQ2.DIOBSZ) AS HQ2SIZE,
COUNT(HQ2 ) AS HQ2NBROBJ,
MAX(HQ.DICDAT) AS CHANGEDDATE
FROM ROB/GDIHQOBJ HQ LEFT OUTER JOIN ROB/GDIHQ2OBJ HQ2
USING (DIOBLI, DIOBNM, DIOBTP)
WHERE DIOBLI<>' ' AND DIOBLI <>'QDOC'
GROUP BY DIOBLI
ORDER BY DIOBLI


Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.