Don't do that way. Create another table for Stock & maintain the stock details there.
Update stock data in
Stock
table while
Sales
& Insert/Update stock data in
Stock
Table while
Purchase
. In this way, there's no need for complex joins & it improves the performance because you have stock details in Stock table. And there're more advantages using this way.
Sample stored procedure for Stock update.
CREATE PROCEDURE Usp_SALES
(...arguments...)
BEGIN
INSERT INTO SALES_TABLE(...fields...) VALUES(...values...)
UPDATE STOCK_TABLE SET (...fields...)=(...values...)
END
CREATE PROCEDURE Usp_PURCHASE
(...arguments...)
BEGIN
INSERT INTO PURCHASE_TABLE(...fields...) VALUES(...values...)
UPDATE STOCK_TABLE SET (...fields...)=(...values...)
END