SQL Query to SUM values from column using the same ID from another table

user_14516945 0
Viewed 2022

I have two tables like below;

table1
===========================
| table1_ID | table1_name |
===========================
|     1     |      A      |
|     2     |      B      |
===========================

table2
======================================
| table2_ID | table2_qty | table2_ID |
======================================
|     22    |      4     |     A     |
|     23    |      9     |     A     |
|     24    |     12     |     B     |
|     25    |     23     |     B     |
======================================

and the ouput should look like this:

================================
| table1_ID | name | total_qty |
================================
|     1     |   A  |     13    |
|     2     |   B  |     35    |
================================

"table2 ID , name & the total sum value of 'table2_qty' with the same ID from 'table1_ID'"

I tried this , but the results isn't like what I want.

SELECT table1.table1_ID, table1.table1_name,            
SUM(table2.table2_qty) As total_qty 
FROM table1, table2 
GROUP BY table1.table1_ID, table1.table1_name;

How to get that results in SQL?
Thanks!

ms-access aggregate-functions inner-join sum sql
3 Answers

Here is an option using a correleated subquery:

select t1.*,
    (
        select sum(t2.table2_qty)
        from table2 as t2
        where t2.table2_id = t1.table1_name
    ) as total_qty
from table1 as t1

You can also join and aggregate:

select t1.table1_id, t1.table1_name, sum(t2.table2_qty) as total_qty
from table1 t1
left join table2 t2 on t2.table2_id = t1.table1_name
group by t1.table1_id, t1.table1_name
user_106767160

You can use nested query:

select t1.table1_id, t2.name, t2.total_qty
from table1 t1
join (
    select table2_id name, sum(table2_qty) total_qty
    from table2
    group by table2_id
) t2 on t2.name = t1.table1_name;
user_22432090

Essentially, your attempt is aggregating on a cross join query since you use comma separated tables in FROM clause.

FROM table1, table2

MS Access uses this older SQL syntax since its dialect does not yet support explicit CROSS JOIN to clearly show what you are attempting. I have suggested such support among other features.

Consequently, your aggregation runs on the cartesian product of both tables which pairwise matches every combination of rows in both tables. So likely your groupings and totals are much larger than expected.

To fix, simply turn CROSS JOIN to INNER JOIN:

SELECT t1.table1_ID
     , t1.table1_name
     , SUM(t2.table2_qty) AS total_qty 
FROM table1 t1
INNER JOIN table2 t2
  ON t1.table1_ID = t2.table1_ID
GROUP BY t1.table1_ID
       , t1.table1_name;
user_14224510
Related