I have a SELECT query as below:
x
SELECT basket_id
, b.amount_due - SUM(p.amount_paid) as due_balance
FROM basket_due b
LEFT JOIN basket_payment p USING (basket_id)
WHERE b.customer_id = 4
GROUP BY customer_id, basket_id
ORDER BY basket_id desc
This query gives me an output like this:
+-----------+-------------------+
| basket_id | due_balance |
+-----------+-------------------+
| 16 | 999.50 |
| 15 | 0.00 |
| 13 | 0.00 |
| 11 | 1716.00 |
| 9 | 1210.00 |
| 7 | 140.00 |
+-----------+-------------------+
My question is, how to filter the 0 due records from due_balance
and modify the query to get the rest?
Expecting result should be:
+-----------+-------------------+
| basket_id | due_balance |
+-----------+-------------------+
| 16 | 999.50 |
| 11 | 1716.00 |
| 9 | 1210.00 |
| 7 | 140.00 |
+-----------+-------------------+
I tried it in this way, but it doesn’t work for me.
SELECT basket_id
, b.amount_due - SUM(p.amount_paid) as due_balance
FROM basket_due b
LEFT JOIN basket_payment p USING (basket_id)
WHERE b.customer_id = 4 AND due_balance > 0
GROUP BY customer_id, basket_id
ORDER BY basket_id desc
Advertisement
Answer
Use HAVING Clause or use subquery then add condition outside with WHERE clause.
SELECT basket_id
, b.amount_due - SUM(p.amount_paid) as due_balance
FROM basket_due b
LEFT JOIN basket_payment p USING (basket_id)
WHERE b.customer_id = 4
GROUP BY customer_id, basket_id
Having due_balance > 0
ORDER BY basket_id desc
SELECT t.basket_id
, t.due_balance
FROM (SELECT basket_id
, b.amount_due - SUM(p.amount_paid) as due_balance
FROM basket_due b
LEFT JOIN basket_payment p USING (basket_id)
WHERE b.customer_id = 4
GROUP BY customer_id, basket_id) t
WHERE t.due_balance > 0
ORDER BY t.basket_id desc