Search notes:

SQL Server: beware of integers when using AVG()

When using the avg() aggregate function on an integer, SQL Server will result an integer.

This may or may not be what the user expects.

In order to return the »correct« value in such cases, the integer value should be multiplied by 1.0 to force SQL Server to calculate the average with decimals:

create table tq84_integers (
  grp varchar(1),
  num  integer
);

insert into tq84_integers values ('A', 1);
insert into tq84_integers values ('A', 2);
insert into tq84_integers values ('A', 3);

insert into tq84_integers values ('B', 7);
insert into tq84_integers values ('B', 8);

select
   grp,
   avg(    num) avg_num_beware,
   avg(1.0*num) avg_num_correct
from
   tq84_integers
group by
   grp;

drop table tq84_integers;

Github repository about-MSSQL, path: /sql/select/aggregate/avg/beware-of-integers.sql

Index