Round averages

Question

Post reply

Round averages

Steve Jones - SSC Editor

SSC Guru

Points: 734434
More actions
September 28, 2016 at 10:26 pm

#100940

Comments posted to this topic are about the item Round averages
September 29, 2016 at 12:25 am

This was removed by the editor as SPAM

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

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

morlindk SSCrazy Points: 2294 More actions · Answer 1

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

Neil Burton SSC-Insane Points: 22908 More actions · Answer 2

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 More actions · Answer 3

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: 10727 More actions · Answer 4

paul s-306273

SSChampion

Points: 10727

September 29, 2016 at 2:00 am

#1903842

Seems straightforward to me.

DaveJenkins Mr or Mrs. 500 Points: 568 More actions · Answer 5

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 Default port Points: 1483 More actions · Answer 6

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 More actions · Answer 7

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 SSCommitted Points: 1544 More actions · Answer 8

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 More actions · Answer 9

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.

GaryV Hall of Fame Points: 3307 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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: 2801 More actions · Answer 13

The answer is not correct. 9.0 is not 9.000000. The precision is different. 😀