why multiply two column in sql is not equals to sum(column1 * column2)

Question

if have these tables :

orders(amount,unit_price,child_amount,child_price,service_id,issue_date,is_success)

pools(name,for_view)

services(id,pool_id)

when i run this sql :

SELECT SUM(orders.amount) AS totalAmount, 
SUM(orders.unit_price) AS totalPrice,  
SUM(orders.amount*orders.unit_price) AS total,  
SUM(orders.child_amount) AS totalAmountC,  
SUM(orders.child_price) AS totalPriceC,  
SUM(orders.child_amount*orders.child_price) AS totalC,  
pools.name, 
pools.for_view 
FROM orders INNER 
JOIN services ON services.id = orders.service_id  
INNER JOIN pools ON pools.id = services.pool_id 
WHERE issue_date = '2024-03-15' 
AND pools.id = '35' 
AND is_success = 1  
GROUP BY pools.name, pools.for_view; 

totalAmount * totalPrice is NOT equals to total !!!!!

look at the result in phpmyadmin when i run the sql :

why these two column is not equals ? total should be 5.000.000 but its wrong ! how can i fix it ?

Answer 1

For get total = 5.000.000 you should replace :

SUM(orders.amount*orders.unit_price) AS total,

by :

SUM(orders.amount)*SUM(orders.unit_price) AS total,

as NickW said this is different...

Answer 2

Because the sum of products does not equal the product of sums. This can easily be seen with a simple example:

Amount	unit price	Amount*unit price
2	10	20
3	20	60
Total		80

Sum amount	sum unit price	total
5	30	150