CODEWITHSUNDEEP
mysqljoinsum
Josef
Josef

Reputation: 2720

Calculcation from two tables

I have two tables. One table contains table with codes and initial quantity

Table T1:

+-------------+------------+
| Code        | QTY        |
+-------------+------------+
| a           | 1          |
+-------------+------------+
| a           | 1          |
+-------------+------------+
| a           | 1          |
+-------------+------------+
| b           | 1          |
+-------------+------------+
| c           | 5          |
+-------------+------------+

Second table T2 contains code and how much pieces are out

Table T2:

+-------------+------------+
|Code         | qty        | 
+-------------+------------+
| a           | 1          |              
+-------------+------------+
| c           | 2          |                
+-------------+------------+

Now I need to make output with current qunatities, how much code are available and output should look like this:

Output:

+-------------+------------+
| Code        | QTY        |
+-------------+------------+
| a           | 2          |
+-------------+------------+
| b           | 1          |
+-------------+------------+
| c           | 3          |
+-------------+------------+

I had tried with this:

SELECT t1.code, (SUM(t1.qunatity) - SUM(t2.qunatity)) AS Avilable
FROM  table t1
LEFT JOIN table2 t2 ON t2.code = t1.code
GROUP BY t1.code

The output that I get is:

Output:

+-------------+------------+
| Code        | Available  |
+-------------+------------+
| a           | 0          |
+-------------+------------+
| b           | null       |
+-------------+------------+
| c           | 2          |
+-------------+------------+

Which is not correct, I should get for a = 2, b = 0. Only c is in this case correct. What I'm doing wrong?

Upvotes: 2

Views: 51

Answers (3)

Sebastian Brosch
Sebastian Brosch

Reputation: 43604

You can try the following solution:

SELECT t1.code, (IFNULL(t1.qty, 0) - IFNULL(t2.qty, 0)) AS 'Available' FROM (
    SELECT code, SUM(qty) AS qty FROM table1 GROUP BY code
)t1 LEFT JOIN (   
    SELECT code, SUM(qty) AS qty FROM table2 GROUP BY code
)t2 ON t1.code = t2.code;

demo: http://sqlfiddle.com/#!9/7b124/1/0

To SELECT only codes with quantity greater than 0 you can use the following query:

SELECT t1.code, (IFNULL(t1.qty, 0) - IFNULL(t2.qty, 0)) AS 'Available' FROM (
    SELECT code, SUM(qty) AS qty FROM table1 GROUP BY code
)t1 LEFT JOIN (   
    SELECT code, SUM(qty) AS qty FROM table2 GROUP BY code
)t2 ON t1.code = t2.code
WHERE (IFNULL(t1.qty, 0) - IFNULL(t2.qty, 0)) > 0;

demo: http://sqlfiddle.com/#!9/7b124/1/1

Upvotes: 2

soft87
soft87

Reputation: 511

SELECT tt.code, sum(tt.qq)
FROM (
    SELECT code, sum(qty) as qq
    FROM tbl_1
    GROUP BY code
    UNION
    SELECT code, sum(-qty) as qq
    FROM tbl_2
    GROUP BY code) as tt
GROUP BY code

DEMO

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133400

Try using a join on the aggregated resul for avoi the relation pruduct between rows 

select  tt11.code, (tt1.t1_quantity - tt2.t2_quantity) AS Available
FROM  (
 select  t1.code, SUM(t1.quantity) as t1_quantity
    FROM  table t1
    group by t1.code
    )  tt1 
left join (
    select  t2.code, SUM(t2.quantity) as t2_quantity
    FROM  table t2
    group by t2.code
    ) tt2 on tt2.code = tt1.code

Upvotes: 2

Related Questions