CODEWITHSUNDEEP
mysqlsqljoinsum
Josef
Josef

Reputation: 2726

SQL - How can I JOIN two tables and SUM a column based on IDs between them?

I have two tables. One table is with master data

Table tbl1:

+-------------+------------+------------+
| ID          | Name       | Total      | 
+-------------+------------+------------+
| 1           | a          | 10         |
| 2           | b          | 5          |
| 3           | c          | 4          |
| 4           | a          | 4          |
+-------------+------------+------------+

Second table tbl2 contains child data. The key between tables is ID

Table tbl2:

+-------------+------------+
|id           | qty        | 
+-------------+------------+
| 1           | 4          |              
| 1           | 3          |                
| 1           | 1          |                
| 3           | 1          |                
| 3           | 3          |                
+-------------+------------+

I need to get output like this:

Output:

+-------------+------------+------------+
| name        | sum_tot    | sum_qty    | 
+-------------+------------+------------+
| a           | 14         | 8          |
| b           | 5          | 0          |
| c           | 4          | 4          |
+-------------+------------+------------+

I had tried with this:

select tbl1.name, SUM(tbl1.total), SUM(tbl2.qty)
from tbl1
left join tbl2 ON tbl1.id = tbl2.id
GROUP by tbl1.name

The output that I get is:

Output:

+-------------+------------+------------+
| name        | sum_tot    | sum_qty    | 
+-------------+------------+------------+
| a           | 34         | 8          |
| b           | 5          |null        |
| c           | 8          | 4          |
+-------------+------------+------------+

Which is not correct.

Here is the sql fiddle:

enter image description here

The summary from first table is not in relation with second table. It seems that somehow query runs three times.

Upvotes: 4

Views: 2926

Answers (3)

jarlh
jarlh

Reputation: 44776

You can simply have a correlated sub-query that calculates the tbl2 sum:

select tbl1.name, 
       SUM(tbl1.total), 
       SUM(COALESCE((select SUM(tbl2.qty) 
                     from tbl2
                     where tbl1.id = tbl2.id), 0)) as qty_tot
from tbl1
GROUP by tbl1.name

Upvotes: 4

ceferrari
ceferrari

Reputation: 1677

SELECT A.name, SUM(A.total) as sum_tot, COALESCE(B.sum_qty, 0) as sum_qty
FROM tbl1 A
LEFT JOIN (
    SELECT id, SUM(qty) as sum_qty
    FROM tbl2
    GROUP BY id
) B ON B.id = A.id
GROUP BY A.name

Upvotes: 4

csharplova
csharplova

Reputation: 117

select tbl1.name, SUM(tbl1.total), SUM(COALESCE(tbl2.qty, 0))
from tbl1
left join tbl2 ON tbl1.id = tbl2.id
GROUP by tbl1.name

Upvotes: 0

Related Questions