SUM in just one table - Mysql

177

February 04, 2018, at 03:44 AM

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 |

Answer 1

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.

POPULAR ONLINE

Flask with ajax response is not redirecting

Couple of errors

How can I use IndexOf with Scanner?

Possible to use with statement withe wx.DirDialog()

Getting error from AJAX response of “:” [on hold]

The MAX(Date) returned incorrect column data in MySQL

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)?

158

MySQL

How to avoid having clause into query?

I am working in this query that runs succesfully

163

MySQL

Quiz Game Score

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...

189

MySQL

Memory use when using mysql and when use file function [on hold]

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 ?

185