T-SQL Troubleshooting: AVG() function doesn't show the correct value in SQL
Introduction
In this article, we're going to explain how to get accurate average value in SQL like the average value in DAX.
Scenario
In Power BI, we are using a DAX AVERAGE function in a measure as the following:
Average Rate = AVERAGE(customers[rate])
In SQL, we have tried to apply the same logic using T-SQL as the following:
select avg(rate) from customers
Unfortunately, We have noted that the output average value in DAX is (10.6) that is not the same average value in T-SQL (10)!
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 a float value shown with fractions.
Show AVG() in T-SQL with a fraction
Consider , we have the below table that has the below columns:
- Rate (float).
- RateInt (Integer).
Table Customers
https://debug.to/?qa=blob&qa_blobid=12025286186977679834
Let's now run the average function for each column based on its datatype.
Float Column datatype
Because of the data type of "rate" column is float, so the output will be shown in a float with a fraction format.
select avg(rate) from Customer -- AVG with a fraction because the column data type is float.
Output
(1.5+1.2+1.5)/3=1.4
Integer Column datatype
For the integer data type fields the average value will be shown as integer and it will ignore any fractions!
select avg(rateint) from Customer -- AVG without a fraction because the column data type is int.
Output
(1+3+4)/3 = 2
Workaround to show AVG() function with a fraction
As a workaround to show the integer field with a fraction, you should cast the field to a float/decimal datatype as the following:
select avg(cast(rateint as float)) from Customer
Applying Casting Output
(1+3+4)/3 = 2.6666
AVG() function Result
Below is a result for AVG() function based on the column data type.
Applies To
- SQL Server.
- DAX.
Conclusion
In conclusion, we have Troubleshooting why AVG() function doesn't show the correct value in SQL?