# Inaccurate Average function in SQL

1 like 0 dislike
661 views

edited

In SQL Server, I have SQL Query to get the average rate using AVG() function in T-SQL

`select avg(rate) from customers`

I am using the same AVERAGE() function in Powerbi DAX query

`avgrate = AVERAGE(customers[rate])`

I noted that the calculated value in DAX PowerBI is NOT the same as the AVG() function in T-SQL!

• AVG() in T-SQL: 5
• AVERAGE() in DAX Powerbi: 5.5
I have two questions
• Why AVG() in T-SQL doesn't show fraction?
• Which AVG() function is accurate? and Why?

2 like 0 dislike
by 54 119 241
edited

## Why AVG() in T-SQL doesn't show fraction?

Actually, the result of AVG() in T-SQL depends on the column datatype!

• If the Column Datatype is an integer, the result will be integer value rounded down.
• If the Column Datatype is a float/decimal, the result will be float value with fractions without any round.

## Show AVG() in T-SQL with a fraction

As a workaround, you can cast the column as a float.

``````select avg(cast(rateint as float)) from Customer -- workaround to show avg with a fraction
``````

Let make it simple and clear by exploring the below example that provides you three AVG queries

Table Customers Float Column datatype

``````select avg(rate) from Customer
-- AVG with a fraction because the column data type is float.
``````

Int Column datatype

`````` select avg(rateint) from Customer
-- AVG without a fraction because the column data type is int.
``````

Workaround to show AVG() function with a fraction

``````select avg(cast(rateint as float)) from Customer
``````

AVG() function Result ## AVG DAX vs AVG SQL

• AVERAGE() In DAX Power BI, it detects the value datatype and calculates it automatically, so it doesn't need to perform additional casting.
• AVG() In SQL Query, it depends on the column datatype and casting is required if you need to show value with a fraction!
by 9 17 28
0 0
Premium answer, thank you for the clear and detail explanation!