Why SQL where greater than is doesn't work?-CodePudding

So, I try to select data where total greater than 3, but is not work, how to fix it?

SQL

SELECT p.image, p.id, p.name, sum(od.qty) AS total, sum(od.price * od.qty) AS nilai
FROM products p, order_details od, orders o 
WHERE  p.id = od.product_id 
AND o.id = od.order_id 
AND o.status = "Finished"
AND total > 6
GROUP BY p.id
ORDER BY nilai DESC 
LIMIT 10

result

Total 6 still there. Any Advice?

CodePudding user response：

Use having group by :

   SELECT p.image, p.id, p.name, sum(od.qty) AS total, sum(od.price * od.qty) AS nilai
    FROM products p, order_details od, orders o 
    WHERE  p.id = od.product_id 
    AND o.id = od.order_id 
    AND o.status = "Finished" 
    GROUP BY p.id , sum(od.qty)
    HAVING sum(od.qty) > 3
    ORDER BY nilai DESC 
    LIMIT 10