Inaccurate Average function in SQL

Question

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?

Answer 1

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!

Read more at T-SQL Troubleshooting: AVG() function doesn't show the correct value in SQL

Comments

Premium answer, thank you for the clear and detail explanation!