Subtract values from same table and two different columns-CodePudding

This is my table with data:

Table input_data:

 ------------- ------------ ------------- ------------- 
| UID         | Code       | QTOut       | QTIn        |
 ------------- ------------ ------------- ------------- 
| A           | 1          | 5000        | 0           |
| A           | 2          | 20000       | 0           |
| A           | 3          | 22000       | 0           |
| A           | 4          | 14000       | 0           |
| A           | 1          | 0           | 5000        |
| A           | 2          | 0           | 4000        |
| A           | 4          | 0           | 11          |
| A           | 1          | 1000        | 0           |
 ------------- ------------ ------------- -------------

And this should be the output:

Table output_data:

 ------------- ------------ ------------- 
| UID         | Code       | Total       |
 ------------- ------------ ------------- 
| A           | 1          | 1000        |
| A           | 2          | 16000       |
| A           | 4          | 22000       | 
| A           | 3          | 13899       |   
 ------------- ------------ -------------

The goal is for each Code to get how much pieces left.

So far I tried on this way but result is 0:

SELECT o.UID, o.Code, (o.QTOut- tt.QTIn) AS Total
FROM input_data o
LEFT JOIN input_data  tt
ON o.UID = tt.UID
WHERE o.UID= 'A' 
GROUP BY o.Code

CodePudding user response：

SELECT uid,
       code,
       Sum(qtout) - Sum(qtin) AS Total
FROM   input_data 
GROUP  BY uid,
          code

CodePudding user response：

Your code was very close. You just needed to aggregate your result using the SUM function.

The WHERE limits your result to the requested [UID].

SELECT o.UID,
       o.CODE,
       SUM(QTOut) - SUM(QTIn) AS Total
FROM   input_data o
WHERE o.UID = 'A'
GROUP  BY UID,
          CODE