Search code examples
mysqlgroup-bysummaxhaving

How to compare sum with the amount?

The table looks like this:

id, price, amount, transactionid
1, 5, 10, abc
2, 5, 10, abc
3, 20, 40, def
4, 20, 40, def
5, 15, 40, xyz
6, 20, 40, xyz

I want to compare the sum of the amounts with the amount and only select that are not equal.

Also in the example: 15 + 20 != 40

SELECT sum(price), transactionid FROM payment group by transactionid

Now I need the check with one of the amounts from a row and show only if is unequal.

Solution

  • Set the conditions in the HAVING clause:

    SELECT transactionid,
       SUM(price) total_price,
       MAX(amount) amount
FROM payment 
GROUP BY transactionid
HAVING total_price <> amount;

    See the demo.