Round averages

Question

Round averages

Viewing 12 posts - 16 through 26 (of 26 total)

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

George Vobr SSCrazy Eights Points: 9998 More actions · Answer 1

colin.frame (9/29/2016)
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?

Colin, this is an interesting question. I was moved by it, too.

For explanation, I made an example, the code is given below.

Perhaps it will help you.

DECLARE @x decimal (38,2) = 8.80;

DECLARE @y decimal (10,0) = 1;

DECLARE @z decimal (4,1);

SELECT (@x/@y);-- Simulation of the return type as described in MSDN AVG T-SQL) --

SET @z = (@x/@y);

SELECT @z;

SELECT ROUND (@z, 0);

GO

Results

---------------------------------------

8.800000

(1 row(s) affected)

---------------------------------------

8.8

(1 row(s) affected)

---------------------------------------

9.0

(1 row(s) affected)

Tom_Hogan Hall of Fame Points: 3896 More actions · Answer 2

DaveJenkins (9/29/2016)
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. Sad

My reasoning was the same.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Nice easy question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

September 29, 2016 at 7:13 am

#1903934

Nice question, thanks.

colin.frame Default port Points: 1483 More actions · Answer 5

Thanks George. That triggered a further bit of research.

The puzzle for me was where the extra decimal places came from in the AVG result. I've found the explanation in https://msdn.microsoft.com/en-GB/library/ms190476.aspx. In short, when the divide operator is used with decimals (or a decimal and an integer), the minimum scale of the result is 6.

The actual rule for resultant scale is MAX(6, s1 + p2 + 1) where s1 is the scale of the numerator (aka dividend) and p2 is the precision of the denominator (aka divisor). In our case,

AVG(Scores)

= SUM(Scores)/COUNT(*)

= 42.50 / 5

So, the resultant scale is MAX(6, 2 + 1 + 1) = 6.

😀

Edit: changed MAX(6, 2 + 0 + 1) to MAX(6, 2 + 1 + 1)

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Looked at this and wondered whether it was a trick question and if so which way it should be interpreted, since one interpretation obviously makes option 3 correct whereas the question as written makes option 4 correct unless one assumes that the column name is not the one shown in the data. Decided to take it literally and assume that as the table shows column name "score" and the queries try to access the non-existent column "testscore" the answer had to be that none produce the required result.

Sadly that was the wrong answer - so the "trick" wasn't making us miss an name mismatch, it was expecting us to guess that the column name in the data shown wasn't the column name in the table (which was the name used in the queries).

I think this queston needs to be repaired so that it can be answered without trying to guess either which deliberately misleading trick is being played or whether there is an error in typing.

Tom

timwell SSCertifiable Points: 5315 More actions · Answer 7

timwell

SSCertifiable

Points: 5315

September 29, 2016 at 8:00 am

#1903962

Edit: Missed previous responses.

Revenant SSC-Forever Points: 42467 More actions · Answer 8

Like many others, I also had to guess whether this was a trick question.

Steve, thanks for the idea but please correct those column names.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 9

Column names corrected. We never aim to trick you on a column name in the answer and a set of data. In this case, hand transposing results to the question was a mistake.

Precision also corrected. I must have used some variable initially to get 9.0, since I tested that. Must have changed code slightly when building the answers.

Points awarded back

Rich Mechaber SSChampion Points: 11130 More actions · Answer 10

colin.frame (9/29/2016)
Thanks George. That triggered a further bit of research.
The puzzle for me was where the extra decimal places came from in the AVG result. I've found the explanation in https://msdn.microsoft.com/en-GB/library/ms190476.aspx. In short, when the divide operator is used with decimals (or a decimal and an integer), the minimum scale of the result is 6.
The actual rule for resultant scale is MAX(6, s1 + p2 + 1) where s1 is the scale of the numerator (aka dividend) and p2 is the precision of the denominator (aka divisor). In our case,
AVG(Scores)
= SUM(Scores)/COUNT(*)
= 42.50 / 5
So, the resultant scale is MAX(6, 2 + 1 + 1) = 6.
😀
Edit: changed MAX(6, 2 + 0 + 1) to MAX(6, 2 + 1 + 1)

Thanks for the research posting, Colin. It's things like this that have led me to create a folder of queries demonstrating oddities and frustrations with T-SQL rounding/implicit conversions/CAST. Things like http://www.sqlservercentral.com/questions/132558/[/url]

Rich

George Vobr SSCrazy Eights Points: 9998 More actions · Answer 11

colin.frame (9/29/2016)
Thanks George. That triggered a further bit of research.
The puzzle for me was where the extra decimal places came from in the AVG result. I've found the explanation in https://msdn.microsoft.com/en-GB/library/ms190476.aspx. In short, when the divide operator is used with decimals (or a decimal and an integer), the minimum scale of the result is 6.
The actual rule for resultant scale is MAX(6, s1 + p2 + 1) where s1 is the scale of the numerator (aka dividend) and p2 is the precision of the denominator (aka divisor). In our case,
AVG(Scores)
= SUM(Scores)/COUNT(*)
= 42.50 / 5
So, the resultant scale is MAX(6, 2 + 1 + 1) = 6.
😀
Edit: changed MAX(6, 2 + 0 + 1) to MAX(6, 2 + 1 + 1)

George Vobr SSCrazy Eights Points: 9998 More actions · Answer 12

George Vobr (9/29/2016)
colin.frame (9/29/2016)
Thanks George. That triggered a further bit of research.
The puzzle for me was where the extra decimal places came from in the AVG result. I've found the explanation in https://msdn.microsoft.com/en-GB/library/ms190476.aspx. In short, when the divide operator is used with decimals (or a decimal and an integer), the minimum scale of the result is 6.
The actual rule for resultant scale is MAX(6, s1 + p2 + 1) where s1 is the scale of the numerator (aka dividend) and p2 is the precision of the denominator (aka divisor). In our case,
AVG(Scores)
= SUM(Scores)/COUNT(*)
= 42.50 / 5
So, the resultant scale is MAX(6, 2 + 1 + 1) = 6.
😀
Edit: changed MAX(6, 2 + 0 + 1) to MAX(6, 2 + 1 + 1)

Thanks for Your feedback and detailed explanations and very useful link to MSDN.

Your research explains the problem comprehensively, well done.

Thank You once again.

Best regards.

G. V.