Results 1 to 10 of 10

Thread: Sql avg

  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Sql avg

    Hi guys,

    Any idea why the following code isnt giving the correct average. It should be : 22.66

    declare @@empref as varchar(10)
    declare @@Date as datetime
    Declare @@dayofweek float
    set @@empref='50025192'
    set @@date= '04/09/2012'

    set @@dayofweek=(select datepart (dw, @@date) as dayOfTheWeek)
    if @@dayofweek=1 begin
    set @@dayofweek=8
    end
    select * from tmsuser.tmswrhrs where empref=@@empref and hrscode='REG' and procdate>=@@date-5-@@dayofweek and procdate <@@date-@@dayofweek+2

    select avg(payrate/cast (hours as float)*60) from tmsuser.tmswrhrs where empref=@@empref and hrscode='REG' and procdate>=@@date-5-@@dayofweek and procdate <@@date-@@dayofweek+2


    Hours PayRate

    720 285
    720 285
    720 234.36
    702 277.875
    18 5.859


    Avg
    22.062

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are using float, and what version of SQL Server are you using?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hmmm...
    Try casting as decimal(10, 2) instead.

    Or simply throw some precision onto your constant:

    select avg(payrate/(hours*60.0))
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2011
    Posts
    44
    Thanks for the replies

    As decimal gave same result.

    without cast give 0.00612833333333333

    I am using sql server 2008

    Any other ideas

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because 22.062 is the correct answer. That is the average pay rate. The average of all the individually calculated pay rates.
    What you are calculating to come up with 22.66 is the WEIGHTED average pay rate. It is simply the Total Rate over the Total Hours.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have entered your data in an Excel worksheet and calculated the average. It gives 22,062.

    What makes you think it should be 22.66 ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Sep 2011
    Posts
    44
    Thanks Blindman,

    I don't know what the difference means but do you know how I get the weighted avg to print out? Is there a different function?

    thanks again

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Weighted average should be:
    sum(payrate)/sum(cast (hours as float)*60)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by blindman View Post
    Weighted average should be:
    sum(payrate)/sum(cast (hours as float)*60)
    I think it's
    sum(payrate * cast(hours as float))/sum(cast(hours as float))
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Sep 2011
    Posts
    44

    Resolved

    Thanks guys,

    I used

    select (sum(payrate)/sum(hours)*60.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •