I have the following situation. I'm setting up a stock control system and through it, I control the in and out of products.
In a table I have the products (code, description).
In the other table, record the movement of the products (code, product_code, type [in, out], quantity, date).
When I try to make the calculation of what comes in, the less that comes out, it does not work.
MY QUERY:
SELECT SUM(s1.quantity) - SUM(s2.quantity)
FROM `stock_movement` AS s1
JOIN `stock_movement` AS s2
ON s1.code_product = s2.code_product
WHERE s1.type = 'IN'
AND s2.type = 'OUT';
RESULTS: 80
The correct result here should be (40 + 20) - (10 + 10) = 40, but this query is considering that all inputs are of type IN and OUT, so the result 80.
Anyone have any idea what I'm doing wrong? The table data below are as follows:
TABLE STOCK_MOVEMENT:
| CODE | CODE_PRODUCT | TYPE | QUANTITY | DATA |
| 1 | 30 | IN | 20 | 2018-01-20 |
| 2 | 30 | IN | 40 | 2018-02-03 |
| 3 | 30 | OUT | 10 | 2018-01-20 |
| 4 | 30 | OUT | 10 | 2018-02-03 |
TABLE STOCK:
| CODE | DESCRIPTION |
| 30 | TEST_PRODUCT |
You don't need to self join here, just use conditional aggregation:
SELECT
CODE_PRODUCT,
SUM(CASE WHEN TYPE = 'IN' THEN QUANTITY ELSE 0 END) -
SUM(CASE WHEN TYPE = 'OUT' THEN QUANTITY ELSE 0 END) AS diff
FROM stock_movement
GROUP BY CODE_PRODUCT;
Note that I am aggregating by product. For your particular sample data set, there is only one product, so we could remove GROUP BY
and get the same result. But in practice you might want a solution which handles multiple products.
Firebase Cloud Functions: PubSub, "res.on is not a function"
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
In my query below I got the correct return on MAX(Date) but It has incorrect return on BidModifier columnIs there a line that I need to add so that I can get the correct data corresponds to my MAX(Date)?
I am currently creating a quiz game wherein the questions were stored in the database, there is also a hall of fame which ranked the player scoresMy question is how can I add the value of score in the database if the answer is correct? For example if the answer...
i want do a script in PHP which will be use by million of users, I want to use file_get_contents instead MYSQL queryWhat would you advice me when you want less consomation of memory ?