Re: Need help with SQL group by

[James Perkins <jrperkinsjr@xxxxxxxxx>]

Mike,
You are seeing what you should. The issue is the PLBHRS field. You are
telling it to GROUP BY this field and that is what it's doing. Since 4.00 is
not equal to 1.00, it can't group by that field. You need to SUM() the hours
in order to get 1 record per employee.

SELECT eeun, eedpt, plbemp, eenam, plbbg1, SUM(plbhrs) , plbptp
FROM PRLBH, EEMP WHERE PLBEMP =  EEENO and
   PLBPTP in('CO','OH', '6D','7D','OG') and
   EETDT = 0 and   EEDPT = '081' and PLBWDT = 20090115
GROUP BY PLBEMP,EENAM,PLBBG1, PLBPTP,EEUN, EEDPT
Order by PLBEMP

HTH,
--
James R. Perkins
http://twitter.com/the_jamezp


On Mon, Aug 17, 2009 at 08:45, Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>wrote:

> I have a sql statement that I cannot get to work the way I want.
> The statement as it currently exists is.
>  SELECT eeun, eedpt, plbemp, eenam, plbbg1, plbhrs , plbptp
>  FROM PRLBH, EEMP WHERE PLBEMP =  EEENO and
> PLBPTP in('CO','OH', '6D','7D','OG') and
>  EETDT = 0 and   EEDPT = '081' and PLBWDT = 20090115
> GROUP BY PLBEMP,EENAM,PLBBG1, PLBHRS  , PLBPTP,EEUN, EEDPT
>  Order by PLBEMP
>
> My results look like this
>
> Eeun    eedpt   plbemp  eenam           plbbg1   plbhrs   plbptp
> Y       081     890     Smith, Mike     081     4.00    OH
> Y       081     890     Smith, Mike     081     1.00    OH
> Y       081     1160    Mead,Kurt       081     3.50    OH
> Y       081     1160    Mead,Kurt       081     1.50    OH
>
> I am trying to get 1 record per employee, but I cannot find the correct
> solution.
> What am I missing here?
>
> Mike
>
>
>
>
>
> NOTICE:  This message, including any attachment, is intended as a
> confidential and privileged communication. If you have received this message
> in error, or are not the named recipient(s), please immediately notify the
> sender and delete this message.
> --
> 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.