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

  • Article

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.

https://debug.to/?qa=blob&qa_blobid=14959734712566622886

Applies To

  • SQL Server.
  • DAX.

Conclusion

In conclusion, we have Troubleshooting why AVG() function doesn't show the correct value in SQL?

Reference

See Also