question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked MikhailFirsov-1277 commented

Question on AVG

Hello,

After reading the theory I still don't understand the following:

"The evaluated result of expression determines the return type."

91276-q1.png


The wording "The evaluated result of expression" I interpret as "calculate the average and see what the value you've got - depending ON THE RESULTING VALUE make a decision which return type to use (for example, if the resulting AVG value =5, than the return type = INT; if the value = 5.56, than the return type = decimal and ...). The key point here is that it is the RESULTING VALUE that determines the return type, NOT the type of the expression itself!

The practice is the opposite:
91267-q2.png

AVG(Col2) returns the 2 instead of ~2.7, whilst AVG(Col2) for another table with the Col2 = decimal returns the correct (decimal) result:
91268-q3.png

In other words it looks like the wording should read "the type of expression determines the return type" - NOT the "The evaluated result of expression determines the return type.*".

What am I missing here?

Thank you in advance,
Michael



sql-server-transact-sql
q1.png (30.5 KiB)
q2.png (47.4 KiB)
q3.png (37.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Note that ‘select 19/7’ is 2. SQL has a different calculator.

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

You understanding of the way it works is correct. Both the wording the documentation has and your wording mean the same. First SQL determines the datatype of the expression (before doing the AVG) and then uses the chart to determine the result of doing the AVG. If you are doing the AVG of a column, the datatype of the expression is just the datatype of the column. But if the expression is a calculation, it determines the datatype of that calculation and then uses that to determine the result of the AVG.

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

The expression can contain multiple data types. For example, AVG(1 + 1.0) will return a decimal data. So the sentence "The evaluated result of expression determines the return type" is correct.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered GuoxiongYuan-7218 commented

"VG(1 + 1.0) will return a decimal data" - yes, it will, but it will not in the cases depicted above.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You do not know the type of the expression 1 + 1.0 since it contains both int and decimal. But the evaluated result of expression 1 + 1.0 is a decimal.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I interpret as "calculate the average and see what the value you've got

Given the wording, this interpretation is not unreasonable. However, if I tell you that the data type of an expression is always static, you will realise that this is not what the documentation wants to say.

The data type is always determined before the computation starts. More precisely, it is determined when the statement is compiled.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MikhailFirsov-1277 commented

Hi @MikhailFirsov-1277,

"The evaluated result of expression determines the return type."

You could also understand above as 'The return type is determined by the type of the evaluated result of expression'.

Using your sample table and please refer some examples as below. Hope it could be a little helpful to you.

 select avg(col2) from Table1 --integer inside avg,result should be integer
 --2
 select avg(col2+0.0) from Table1 --float inside avg(add 0.0 which is a fast conversion to float),result should be float
 ----2.714285
 select cast(avg(col2) as decimal(5,2)) from Table1 --integer inside avg but convert result to decimal
 --2.00
 select avg(cast(col2 as decimal(5,2))) from Table1 --decimal inside avg,result should be decimal 
 --2.714285
 select cast(avg(cast(col2 as decimal(5,2))) as decimal(5,2)) from Table1 --decimal inside avg and convert the result to specific decimal(5,2)
 --2.71

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Melissa, thank you so much for the examples!

Regards,
Michael

0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered

Thank you all very much for your explanations!

"If you are doing the AVG of a column, the datatype of the expression is just the datatype of the column. But if the expression is a calculation, it determines the datatype of that calculation and then uses that to determine the result of the AVG."


"The data type is always determined before the computation starts. "

"select avg(col2) from Table1 --integer inside avg,result should be integer" -

  • all that means that the wording "The evaluated result of expression." means the result of expression itself (e.g. in my case the expression" = Col2), NOT the resulting value of AVG calsulation as I thought.

Thank you all once again!

Regards,
Michael



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.