Re: Need help with SQL group by

Mike,

You need you group by the lowest level that you want. The rest of the fields need to be specified with an aggregate type of function that works within a set. This is the simplest way of doing it, but using a CTE or subquery you might get a little fancier.

In your case, let's say the hierarchy is department and employee number: eedpt, plbemp. The SQL statement might look like this:

SELECT max(eeun) as eeun, eedpt, plbemp, max(eenam) as eenam, max(plbbg1) as plbbg1,
sum(plbhrs) as plbhrs, max(plbptp) as 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, EEDPT
Order by PLBEMP


-mark

At 8/17/09 11:45 AM, you 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