CODEWITHSUNDEEP
sqlcastingfloating-pointaverage
CodeKingPlusPlus
CodeKingPlusPlus

Reputation: 16081

SQL AVG returning an int

In one of my queries it appears that the AVG function is returning an int. 

select ..., AVG(e.employee_level)avg_level

How do I get it to return floating point values? I tried casting it but all my rows for avg_level were still integers.

Upvotes: 39

Views: 57414

Answers (4)

gotqn
gotqn

Reputation: 43646

The returned type of the AVG depends on the evaluated type of the expression passed to the function.

Expression result Return type
tinyint int
smallint int
int int
bigint bigint
decimal category (p, s) decimal(38, max(s,6))
money and smallmoney category money
float and real category float

For example, if you want your result to be float, you need to ensure your column or expression type is float.

Upvotes: 4

Digant Dixit
Digant Dixit

Reputation: 9

AVG(Cast(e.employee_level as DECIMAL(10,O))

THIS WILL WORK IN MYSQL YOU ARE CATING THE ANS TO GIVE THE VALUE UPTO 0 DECIMALS SO THIS WILL WORK SUPPOSE THE ANS IS 945.454 SO AVG(Cast(e.employee_level as DECIMAL(10,O)) WILL GIVE 945 AND AVG(Cast(e.employee_level as DECIMAL(10,1)) WILL GIVE 945.5 HOPE YOU GOT THE ANS

Upvotes: -2

Simon Dorociak
Simon Dorociak

Reputation: 33515

Try to do it like this:

AVG(Cast(e.employee_level as Float)) as avg_level

Also i found this topic where you can find some another approach but i not used its and don't know exactly whether works or not.

Upvotes: 60

Philip Kelley
Philip Kelley

Reputation: 40359

Casting is more certain, but ...AVG(1.0 * e.employee_level)... might do it as well, and can be more legible.

Upvotes: 3

Related Questions