Menu
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.
577
The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL ), and a DOUBLE value for approximate-value arguments ( FLOAT or DOUBLE ).
If you are in SQL Server, just use round(avg(column * 1.0), 0) .
SQL AVG() with ROUND(), group byThe SQL ROUND() is used to round the value up to a specific decimal places. The GROUP BY clause with aggregate function makes the result within a group.
The AVG() function returns the average value of a numeric column.
AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value, AVG () will return an error. AVG is a deterministic function when used without the OVER and ORDER BY clauses.
CAST() function inside AVG() function. The SQL AVG() function returns the average value with default decimal places. The CAST() is used to increase or decrease the decimal places of a value. The CAST() function is much better at preserving the decimal places when converting decimal and numeric data types.
Summary: this tutorial, we will show you how to use SQL AVG function to get the average value of a set. The SQL AVG function is an aggregate function that calculates the average value of a set. The following illustrates the syntax of the SQL AVG function:
This is because the SQL AVG () function ignores NULL s and simply calculates the average of the other records with numeric values. That is, it ignores the value from the row shown below. Imagine you have some duplicated data in your tables, and you want to ignore duplicate values when calculating the average.
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.
184
Casting is more certain, but ...AVG(1.0 * e.employee_level)... might do it as well, and can be more legible.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
