Q:

Why does round sometimes produce extra digits?

Occasionally, when selecting aggregate data (usually AVG()) from a database, I'll get a repeating decimal such as:
2.7777777777777777

When I apply ROUND(AVG(x), 2) to the value, I sometimes get a result like:
2.7800000000000002

I happen to know that the actual sample has 18 rows with SUM(x) = 50.  So this command should be equivalent:
SELECT ROUND(50.0/18, 2)

However, it produces the expected result (2.78). Why does rounding sometimes produce wrong results with aggregate functions?

In order to verify the above result, I wrote a query against a dummy table:
declare @temp table(
  x float
)

insert into @temp values (1.0);
insert into @temp values (2.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (8.0);
insert into @temp values (9.0);

select round(avg(x), 2),
       sum(x),count(*)
from @temp

I'm aware of the gotchas of floating point representation, but this simple case seems not to be subject to those.

A:

Decimal numbers don't always (or even usually) map 1:1 to an exact representation in floating point.
In your case, the two closest numbers that double precision floating point can represent are;

40063D70A3D70A3D which is approximately 2.77999999999999980460074766597
40063D70A3D70A3E which is approximately 2.78000000000000024868995751604

There exists no double precision number between those two numbers, in this case the database chose the higher value which - as you see - rounds to 2.7800000000000002.

