38

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.

Taryn
  • 242,637
  • 56
  • 362
  • 405
CodeKingPlusPlus
  • 15,383
  • 51
  • 135
  • 216
  • 3
    The behavior of the AVG() function on integers is not defined in the ANSI standard. Some databases return an integer; other databases return a float. To get a float you can use an explicit cast/convert. Or, what I do is simply multiply by 1.0. – Gordon Linoff Jun 19 '12 at 17:57

4 Answers4

60

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.

Simon Dorociak
  • 33,374
  • 10
  • 68
  • 106
  • 3
    @CodeKingPlusPlus - I guess you tried `CAST(AVG(field) AS FLOAT)`? `AVG(field)` will still return the same answer, which is an INT, by which time casting it to a FLOAT is not going to help you. `AVG(CAST(field AS FLOAT))`, however, is very different, and probably what you want. *[+1 to this answer]* – MatBailie Jun 19 '12 at 17:37
4

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

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • 3
    It depends on the RDBMS. AFAIK many RDBMS treat `1.0` as a fixed point NUMERIC value, not a FLOAT. Similar effect, possibly even better for this case, but not quite the same. For this reason I tend to prefer explicit CAST/CONVERT in my code. Then you *know* what data types things are, rather than relying on data-type precidence and implicit conversions. – MatBailie Jun 19 '12 at 17:40
4

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

enter image description here

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

gotqn
  • 42,737
  • 46
  • 157
  • 243
-1

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

Digant Dixit
  • 1
  • 1
  • Please provide additional details in your answer. As it's currently written, it's hard to understand your solution. – Community Sep 03 '21 at 08:37
  • Hello and welcome! Please have a look at [Why is it considered inappropriate and unprofessional to type in all capital letters?](https://www.quora.com/Why-is-it-considered-inappropriate-and-unprofessional-to-type-in-all-capital-letters). Thank you. – Eric Aya Sep 03 '21 at 13:14