Need help with SQL Group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with SQL Group by

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-17 : 12:06:16

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?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-17 : 12:10:24

What value of plbhrs do you need? If you need the sum...use

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 eeun, eedpt, plbemp, eenam, plbbg1,plbptp
Order by PLBEMP

If you need the maxiumum/minimum value...you can use...

SELECT eeun, eedpt, plbemp, eenam, plbbg1, MAX(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 eeun, eedpt, plbemp, eenam, plbbg1,plbptp
Order by PLBEMP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources