How to display Non Zero Values in MySQL Query

Hi im creating a simple Inventory system for my project. the query below shows me perfectly the Total Stocks remaining for each product. now i have to control the system or the view, if the ‘TOTAL STOCKS’ field(column) becomes zero, i dont want it to show anymore in my query. only those who are greater than zero value

SELECT DISTINCT t1.tbl_locposition AS 'Position',t1.tbl_locname AS 'Location Name', 
t2.tbl_productname AS 'Product', SUM(t0.tbl_qtyin) - SUM(t0.tbl_qtyout)  AS 'Total Stocks', 
t0.tbl_datereceived AS 'Date Received', t0.tbl_datemanufactured AS 'Date Manufactured', 
t0.tbl_threadcolor AS 'Thread Color', t2.tbl_productid AS 'Product ID', t1.tbl_locid AS 'Location 
ID', t3.tbl_truckid AS 'Truck ID', t3.tbl_truckcode AS 'Truck Code' 

FROM tbl_movement t0 
INNER JOIN tbl_products t2 ON t0.`tbl_prodid` = t2.`tbl_productid` 
INNER JOIN tbl_location t1 ON t0.`tbl_locid`  = t1.`tbl_locid` 
LEFT JOIN tbl_trucks t3 ON t0.`tbl_truckid` = t3.`tbl_truckid` 

GROUP BY t0.tbl_locid, t0.tbl_prodid  ORDER BY t0.tbl_datemanufactured ASC

The query works perfectly but when Total Stocks become zero. i want to remove it. anyone who can help? here is the sample picture of the data.

Query Image Sample

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You could use HAVING

INNER JOIN tbl_location t1 ON t0.`tbl_locid`  = t1.`tbl_locid` 
LEFT JOIN tbl_trucks t3 ON t0.`tbl_truckid` = t3.`tbl_truckid`

HAVING SUM(t0.tbl_qtyin) - SUM(t0.tbl_qtyout) > 0;

GROUP BY t0.tbl_locid, t0.tbl_prodid  ORDER BY t0.tbl_datemanufactured ASC


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x