CODEWITHSUNDEEP
mysql
GusDeCooL
GusDeCooL

Reputation: 5758

Conditional from SUM() of another table?

i have data 2 table like this

Table Transaction

ID | Amount
1  | 200
2  | 300

Table Payment

ID | Transaction | Amount
1  | 1           | 200
2  | 2           | 150
3  | 2           | 100

then i using query

SELECT `transaction`.id AS `id`,
    `transaction`.amount AS amount,
    SUM(payment.amount) AS payment,
    `transaction`.amount - SUM(payment.amount) AS balance
FROM `transaction`
LEFT JOIN payment
    ON payment.`transaction` = `transaction`.id
GROUP BY payment.`transaction`;

and then i get data like this

id | amount | payment | balance
1  | 200    | 200     | 0
2  | 300    | 250     | 50

Now how do i make conditional to only show data which balance is more than 0?
Which is should only show row id 2

i tried do this query, but it give me error.

SELECT `transaction`.id AS `id`,
    `transaction`.amount AS amount,
    SUM(payment.amount) AS payment,
    `transaction`.amount - SUM(payment.amount) AS balance
FROM `transaction`
LEFT JOIN payment
    ON payment.`transaction` = `transaction`.id
WHERE `transaction`.amount - SUM(payment.amount) > 0
GROUP BY payment.`transaction`;

Thank you,
Gusde

Upvotes: 1

Views: 142

Answers (1)

Daniel Von Fange
Daniel Von Fange

Reputation: 6071

The "HAVING" SQL clause does what you are looking for. It like "WHERE", except it runs after the JOINS and GROUP BY's, and allows you filter based on the almost final output. It's also useful for finding missing relationships between tables you've joined together.

SELECT `transaction`.id AS `id`,
   `transaction`.amount AS amount,
    SUM(payment.amount) AS payment,
    `transaction`.amount - SUM(payment.amount) AS balance
FROM `transaction`
LEFT JOIN payment
    ON payment.`transaction` = `transaction`.id
GROUP BY payment.`transaction`
HAVING balance > 0;

Upvotes: 2

Related Questions