Filtering empty rows from Mysql SELECT query issue-CodePudding

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

CodePudding user response：

The non zero assertion on the due balance belongs in a HAVING 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;

CodePudding user response：

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

CodePudding user response：

You need to use having

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

CodePudding user response：

Try to use <> 0. Should be work.