Question on AVG

Question

Hello,

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

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

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:

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

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

Hi @Mikhail Firsov ,

"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.