Re: Need help with SQL group by

["M. Lazarus" <mlazarus@xxxxxxxx>]

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