while summing of column and joining two tables returns double count

Question

I have two table and i have performed inner joined on them: Below is my query

SELECT a.ship_id AS ship_id,
       sum(a.qty) as total_qty
from table_a as a
inner join table_b on a.shp_id = b.shpid
group by a.shp_id

Sample data:

table_a
-----------------------------
product_name | qty | ship_id
-----------------------------
item_1       | 10  | 1
item_2       | 20  | 1
item_3       | 10  | 2
item_4       | 10  | 2

table_b
-------------------
ship_id | desc
-------------------
1       | desc_1
2       | desc_2

What i am getting output of above query is as follows:

--------------------
ship_id | total_qty
--------------------
1       | 60
2       | 40 

Expected output:

--------------------
ship_id | total_qty
--------------------
1       | 30 
2       | 20

Can anyone please help me how to get this.

Answer 1

Try the below way -

select a.ship_id,totalqty
from
(SELECT ship_id AS ship_id,
       sum(qty) as totalqty
from table_a group by ship_id) as a
inner join table_b on a.shp_id = b.shpid