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

user_1866833 0
Viewed 1798

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.

join sum mysql sql
3 Answers

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
user_37060160 
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
user_23871900 
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
user_42243030
Related