Re: Need help with SQL group by

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.