[SOLVED] Subtract values from same table and two different columns

Issue

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

Solution

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

Answered By – RF1991

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.