Search code examples
sqljoinphpmyadminsum

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

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 :

enter image description here

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

Solution

  • 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...