×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Count/Sum not working properly, I think

Count/Sum not working properly, I think

Count/Sum not working properly, I think

(OP)
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.
Replies continue below

Recommended for you

RE: Count/Sum not working properly, I think

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 --

CODE

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

RE: Count/Sum not working properly, I think

(OP)
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.

RE: Count/Sum not working properly, I think

(OP)
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.

RE: Count/Sum not working properly, I think

(OP)
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.

RE: Count/Sum not working properly, I think

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close