skip to Main Content

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 ?

2

Answers


  1. 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
    Login or Signup to reply.
  2. 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…

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search