How to only display rows that have a column value of greater than zero?-CodePudding

I've been working on my first MySQL query that I can put to use and I'm stuck. I've been able to query the data I want but am not able to exclude rows that have a value of 0 or less in a specific column. To create the column in question (Qty Remaining), I used the subtraction operator (-) in the SELECT statement. I'm hoping that's a reasonable way to do it but my attempts to exclude values of 0 or less have been unsuccessful. Query below:

SELECT 
    po.num AS "PO Number",
    DATE_FORMAT(poitem.dateScheduledFulfillment,"%c/%e/%Y") AS "Date Scheduled",
    poitem.partNum AS "Part Number",
    poitem.description AS "Part Description",
    (poitem.qtyToFulfill - poitem.qtyFulfilled) AS "Qty Remaining",
    poitemstatus.name AS "Status"
FROM poitem
JOIN poitemstatus ON poitemstatus.id=poitem.statusId
JOIN po ON po.id=poitem.poId
WHERE poitem.statusId="30" OR poitem.statusId="10" 
    AND NOT poitem.typeid="11"
    AND NOT poitem.typeid="21"
ORDER BY "Date Scheduled"

I've tried a some different AND statements to exclude the rows in question. The statements without quotes around the 0 seem to exclude way too many rows and still include rows with zeros. The statements with quotes don't seem to affect the results.

AND "Qty Remaining" > 0
AND "Qty Remaining" <> 0
AND "Qty Remaining" > "0"
AND "Qty Remaining" <> "0"

CodePudding user response：

In SQL, you need single quotes ('), not double quotes ("), for string values. The SQL standard reserves double quotes for object names, so AND NOT poitem.typeid="11" is trying to compare poitem.typeid with a column named "11".

Additionally, the OR was probably not doing what you expected; the operator precedence here tends to surprise people. You pretty much always need a set of parentheses around an OR comparison, but in this case we can re-write it using IN().

WHERE poitem.statusId IN (30, 10)
    AND poitem.typeid NOT IN (11, 21)
    AND (poitem.qtyToFulfill - poitem.qtyFulfilled) > 0