Count/Sum not working properly, I think

[OmenChild]

Hello all,
I've been working on a query and I'm attempting to shorten the results. Without the count or sum functions, I get 450 records. When I use the count or sum functions, I shorten the amount of records, but the function isn't doing what I need it to. I have a feeling it is the way I am using these functions. Here is the query:

SELECT DISTINCT A.EMPLID, A.NAME, A.PAYGROUP, A.DEPTID, B.ERNCD, C.DESCR, B.RATE_USED, COUNT(B.OTH_HRS) AS OTH_HRS,
B.OTH_PAY, SUM(B.OTH_EARNS) AS OTH_EARNS, B.PAY_END_DT

FROM PS_PAY_CHECK A INNER JOIN PS_PAY_OTH_EARNS B
ON A.COMPANY = B.COMPANY
AND A.PAYGROUP = B.PAYGROUP
AND A.EMPLID = '000012226'
AND A.DEPTID = '799400'
AND B.ERNCD = 'SKH'
AND B.PAY_END_DT BETWEEN '2007-01-01' AND CURRENT DATE
LEFT OUTER JOIN PS_EARNINGS_TBL C
ON B.ERNCD = C.ERNCD
WHERE C.EFFDT = (SELECT MAX (C1.EFFDT)
FROM PS_EARNINGS_TBL C1
WHERE C.ERNCD = C1.ERNCD
AND C1.EFFDT <= CURRENT DATE)

GROUP BY A.EMPLID, A.NAME, A.PAYGROUP, A.DEPTID, B.ERNCD, C.DESCR, B.RATE_USED, B.OTH_PAY, B.PAY_END_DT



My results are really scewed when I run it. For example, these are the results without the functions:


NAME PAYGROUP DEPTID ERNCD OTH_HRS OTH_EARNS PAY_END_DT
Torres,Efren CW3 799400 SKH 8.00 0.00 2007-01-07
Torres,Efren CW3 799400 SKH 16.00 0.00 2007-01-07
Torres,Efren CW3 799400 SKH 8.00 148.00 2007-01-07
Torres,Efren CW3 799400 SKH 8.00 196.96 2007-01-07
Torres,Efren CW3 799400 SKH 16.00 424.00 2007-01-07


These are the results with the functions:

NAME PAYGROUP DEPTID ERNCD OTH_HRS OTH_EARNS PAY_END_DT

Torres,Efren CW3 799400 SKH 7616.00 104578.56 2007-01-07

Torres,Efren CW3 799400 SKH 23392.00 174910.96 2007-01-14
Torres,Efren CW3 799400 SKH 5712.00 39236.00 2007-01-21
Torres,Efren CW3 799400 SKH 23528.00 219018.48 2007-01-28


As you can see for the PAY_END_DT, the amounts are incorrect. Any suggestions? Thank you much.

 

[r937]

i've re-arranged a number of your conditions to be more in line with what i think your intentions are

let me know how this goes --

SELECT A.EMPLID
     , A.NAME
     , A.PAYGROUP
     , A.DEPTID
     , B.ERNCD
     , C.DESCR
     , B.RATE_USED
     , COUNT(B.OTH_HRS) AS OTH_HRS
     , B.OTH_PAY
     , SUM(B.OTH_EARNS) AS OTH_EARNS
     , B.PAY_END_DT
  FROM PS_PAY_CHECK A 
INNER 
  JOIN PS_PAY_OTH_EARNS B
    ON B.COMPANY = A.COMPANY
   AND B.PAYGROUP = A.PAYGROUP
   AND B.ERNCD = 'SKH'
   AND B.PAY_END_DT BETWEEN '2007-01-01' AND CURRENT_DATE
LEFT OUTER 
  JOIN PS_EARNINGS_TBL C
    ON C.ERNCD = B.ERNCD
   AND C.EFFDT = 
       ( SELECT MAX(EFFDT)
           FROM PS_EARNINGS_TBL 
          WHERE ERNCD = C.ERNCD
            AND EFFDT <= CURRENT_DATE )
 WHERE A.EMPLID = '000012226'
   AND A.DEPTID = '799400'
GROUP 
    BY A.EMPLID
     , A.NAME
     , A.PAYGROUP
     , A.DEPTID
     , B.ERNCD
     , C.DESCR
     , B.RATE_USED
     , B.OTH_PAY
     , B.PAY_END_DT

r937.com | rudy.ca

 

[OmenChild]

Thanks, but it is still returning the same incorrect data. The order of my requests need to be that way. It didn't like when I switched up the EMPLID and the DEPTID placement. It kept giving me an ON clause error. I'll keep digging. If you think of anything else, please let me know.

 

[PHV]

What are the results without the functions and without the DISTINCT predicate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OmenChild]

These are the results without the functions, but with the distinct. When I don't use distinct I get a bunch of repeats:


NAME PAYGROUP DEPTID ERNCD OTH_HRS OTH_EARNS PAY_END_DT
Torres,Efren CW3 799400 SKH 8.00 0.00 2007-01-07
Torres,Efren CW3 799400 SKH 16.00 0.00 2007-01-07
Torres,Efren CW3 799400 SKH 8.00 148.00 2007-01-07
Torres,Efren CW3 799400 SKH 8.00 196.96 2007-01-07
Torres,Efren CW3 799400 SKH 16.00 424.00 2007-01-07


When I use the sum function, it should come out as 56 hrs for OTH_HRS, and it should come up with 768.96 for the OTH_EARNS. Unfortunately, this is not what is coming out.

 

[PHV]

You have to know why you've got a lot of dups without the DISTINCT predicate !

BTW, you really need an OUTER join for PS_EARNINGS_TBL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OmenChild]

Doesn't an inner join bring back your results faster? That was always how I understood it. And yes, I do know why I get a bunch of dups when I don't use distinct.

 

[PHV]

So, if you do know, where is the problem ?
Anyway you may base your aggregate query on a subquery (inline view) returning the distinct values.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OmenChild]

The problem was in my joins. Thanks for the help!