Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
1 like 0 dislike
661 views
in SQL Queries by 9 17 28
edited by

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?

1 Answer

2 like 0 dislike
by 54 119 241
edited by
 
Best answer

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

AVG function in T-SQL doesn't show fraction

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

Show AVG() in T-SQL with a fraction


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

by 9 17 28
0 0
Premium answer, thank you for the clear and detail explanation!
If you don’t ask, the answer is always NO!
...