MySQL convert column data type from DECIMAL(10,5) to DECIMAL(10,2) shows different SUM outcomes

I have a table that holds a net_total column with data type DECIMAL(10,5). I'd like to convert it to DECIMAL(10,2) but the output of SELECT SUM before and after are different.

Sample DB Fiddle: https://www.db-fiddle.com/f/2cq6j9Up2w5hnwQke2JVAU/1

INSERT INTO `sales_items` (`id`, `net_total`) VALUES
(1, '2.21239'),
(2, '0.88496'),
(3, '1.76991'),
(4, '15.92920'),
(5, '3.25203'),
(6, '3.53982'),
(7, '16.81416'),
(8, '9.73451'),
(9, '1.32743'),
(10, '2.65487'),
(11, '0.88496'),
(12, '2.21239'),
(13, '7.96460'),
(14, '33.62832'),
(15, '33.62832'),
(16, '17.88618'),
(17, '2.65487'),
(18, '1.32743'),
(19, '1.32743'),
(20, '2.21239'),
(21, '0.88496'),
(22, '1.76991'),
(23, '33.62832'),
(24, '1.76991'),
(25, '3.53982'),
(26, '5.30973'),
(27, '2.65487'),
(28, '2.21239'),
(29, '0.88496'),
(30, '1.76991'),
(31, '6.63717'),
(32, '38.93805'),
(33, '2.65487'),
(34, '7.31707'),
(35, '5.75221');

If I sum the values of this table (SELECT SUM(net_total) FROM sales_items) it returns 277.57032, which is basically 277.57.

Before applying ALTER on the column, I round the values first:

2 decimal places = 277.53000 => UPDATE sales_items SET net_total = ROUND(net_total, 2);
3 decimal places = 277.56800 => UPDATE sales_items SET net_total = ROUND(net_total, 3);

Imediatelly we can see that if we round to 2 decimal places, there's a problem. If we round to 3 decimal places it's fine (?) if rounded further.

After rounding either with ROUND(, 2) or ROUND(, 3), I change the column type: ALTER TABLE sales_items CHANGE net_total net_total DECIMAL(10,2) NOT NULL;

The result of the SELECT SUM now is:

2 decimal places = 277.53
3 decimal places = 277.63

Why does this happen and how can I fix it? The correct value is 277.57.

Solution

Just like the new sum won't be exactly 277.57032, it won't be 277.57 either. When you round, some values will be rounded up, increasing the sum, and some rounded down, decreasing the sum. The overall change to the sum will vary from your 277.57 based on whether more rows were rounded up or down, and by how much. And changing the number of digits changes which way they round, so the 3 digit one will have a different balance of up vs down.

If you want the sum to end up 277.57, you can choose to make your data lie. With 2 decimal places, slightly too many values are rounding down; change to something like set net_total = round(net_total + .0001, 2), and adjust that .0001 number until it gets you your target sum.