Re: Multiple counts when joining files

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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.