Round averages

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

    Comments posted to this topic are about the item Round averages

  • morlindk

    SSCrazy

    Points: 2294

    I think 'None of these queries' is correct.

    (i) Table has Score, queries reference TestScore => Error

    (ii) Even option 3 produces '9', not '9.0'

    /morten

  • This was removed by the editor as SPAM

  • Neil Burton

    SSC-Insane

    Points: 22228

    morlindk (9/29/2016)


    I think 'None of these queries' is correct.

    (i) Table has Score, queries reference TestScore => Error

    (ii) Even option 3 produces '9', not '9.0'

    /morten

    Option 3 produces 9.000000 for me and I noticed the testscores column too. I did spend a bit of time second guessing myself before I decided that the intent was to show the process not the tricks.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    β€”Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Joseph-411461

    Ten Centuries

    Points: 1228

    From the scientific point of view, the numbers 9.00000 and 9.0 and 9 are significantly different results. In this case, only 9 is correct, because the average was rounded to 0 decimals. This is not just a typo like the wrong column name, it's a conceptual difference we should keep in mind when working with real numbers. That's why I'd say, only the last answer is correct. My PC, however, would perfectly recognize Round(Avg()) as equal to 9.0, so I must admit that, from the SQLServer point of view, I'm wrong.

    Joseph

    "Science is the history of beautiful theories killed by ugly facts."

  • paul s-306273

    SSChampion

    Points: 10615

    Seems straightforward to me.

  • DaveJenkins

    Mr or Mrs. 500

    Points: 568

    morlindk (9/29/2016)


    I think 'None of these queries' is correct.

    (i) Table has Score, queries reference TestScore => Error

    (ii) Even option 3 produces '9', not '9.0'

    /morten

    Totally agree, I got this wrong. My gut was option 3, ran the code to make sure as I don't like to assume. It produced 9, which is not 9.0, so chose none of the above - which meant I then got the question wrong. πŸ™

  • colin.frame

    Ten Centuries

    Points: 1373

    Why do we get an answer like "9.000000" instead of "9" when the length is 0 (i.e. rounded to no decimal points)? The answer here has two parts. I think I can explain one, but not the other.

    Part 1: empirical testing shows that ROUND returns a data type with the same number of decimal points as the input numeric expression, and the AVG result has several decimal points (result is "8.500000" in my case), hence a result of 9 followed by a decimal point and six zeroes. It grates a bit that the result is given this way as it gives a false impression of accuracy, but I can accept it in this context.

    Part 2: some help here please? Why does AVG return a figure to 6 decimal places when all the input numbers only have 2? In my table, numeric precision is 3 and numeric scale is 2. MSDN article AVG (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms177677.aspx states that the return type of AVG is decimal(38, s) divided by decimal(10, 0). How does that translate in this case?

    Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.

  • GazzaB

    Old Hand

    Points: 391

    morlindk (9/29/2016)


    I think 'None of these queries' is correct.

    (i) Table has Score, queries reference TestScore => Error

    (ii) Even option 3 produces '9', not '9.0'

    /morten

    Have to agree, I thought the same!

  • smw147

    Ten Centuries

    Points: 1387

    morlindk (9/29/2016)


    I think 'None of these queries' is correct.

    (i) Table has Score, queries reference TestScore => Error

    (ii) Even option 3 produces '9', not '9.0'

    /morten

    I think the correct answer is meant to be

    SELECT ROUND(AVG(testscore), 0) FROM dbo.Scores

    But it is clearly not because there is no column called testscore (this is a typo) πŸ™‚

    Regards

    Steve

  • morlindk

    SSCrazy

    Points: 2294

    Agree, - but along a general line of reasoning, I think a catch all option like 'None of the above' is easy to add - but dangerous - since it comes into play whenever some inconsistency has otherwise been overlooked. - I have been doing MS Certification tests for a decade, or so, and notice the questions are often phrased like "which of the below options best suit the situation". A good way to preempt cases like this.

  • gvoshol 73146

    Hall of Fame

    Points: 3188

    My "None of the above" story:

    I was taking an online test. The test could be repeated 3 times until you passed. So you didn't just remember the answers of questions you already got right, the order of the questions was randomized. And the order of the answers in questions was randomized.

    One question came up with "A. None of the above".

  • Ernie Schlangen

    SSCrazy

    Points: 2382

    morlindk (9/29/2016)


    I think 'None of these queries' is correct.

    (i) Table has Score, queries reference TestScore => Error

    (ii) Even option 3 produces '9', not '9.0'

    /morten

    +1

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    I tried this a few ways. The following

    select avg(x), avg(round(x, 0)), round(avg(x), 0) from (values(9.5), (9.00), (8.50), (8.00), (7.50)) a (x);

    resulted in the following error which was surprising to me:

    Msg 8115, Level 16, State 2, Line 1

    Arithmetic overflow error converting expression to data type numeric.

    I traced this to it assigning a data type of numeric(3, 2). This strikes me as a bug in SQL Server because it seems like it should be able to do the calculation.

    The following returned 8.5, 8.8 and 9:

    select avg(cast(x as float)), avg(round(cast(x as float), 0)), round(avg(cast(x as float)), 0) from (values(9.5), (9.00), (8.50), (8.00), (7.50)) a (x);

    And finally the following returned 8.500000, 8.800000, and 9.000000

    select avg(cast(x as numeric(4, 2))), avg(round(cast(x as numeric(4, 2)), 0)), round(avg(cast(x as numeric(4, 2))), 0) from (values(9.5), (9.00), (8.50), (8.00), (7.50)) a (x);

    But if I repeat the input values several times and then try to run the calculation it still comes up with an overflow error.

  • Bobby Russell

    SSCrazy

    Points: 2667

    The answer is not correct. 9.0 is not 9.000000. The precision is different. πŸ˜€

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply