I have a SELECT query as below:
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