LEFT JOIN generating the wrong SUM

I am trying to do a simple join of two different tables stock_tracker_max and tbl_shipment_data_final. However, one of the SUMs I get post join is completely wrong.

Query 1:

SELECT 

a.Stock_Tracker
,SUM(a.Qty_Invoiced) as Qty_Invoiced
,SUM(a.Qty_Received) as Qty_Received


FROM `strange-bird-366417.financial_data.stock_tracker_max` a

where (a.stock_tracker) ='WK06 GABONA'
group by 1

Query 2:

SELECT 

b.Stock_Tracker
,SUM(b.Qty_Shipped) as Qty_Shipped


FROM `strange-bird-366417.financial_data.tbl_shipment_data_final`  b 

where (b.stock_tracker) ='WK06 GABONA'
group by 1

Query 3:

SELECT 

a.Stock_Tracker
,b.Stock_Tracker
,SUM(a.Qty_Invoiced) as Qty_Invoiced
,SUM(a.Qty_Received) as Qty_Received
,SUM(b.Qty_Shipped) as Qty_Shipped


FROM `strange-bird-366417.financial_data.stock_tracker_max` a LEFT JOIN (SELECT Stock_Tracker,Sum(Qty_Shipped) as Qty_Shipped from `strange-bird-366417.financial_data.tbl_shipment_data_final` group by 1) b 
on a.Stock_Tracker = b.Stock_Tracker 

where (a.stock_tracker) ='WK06 GABONA'
group by 1,2

I have simplified the task for the purposes of this exercise (this affects more than just one Stock_Tracker but I just filtered to make it simpler to explain). The first two queries in the above are when I query each table separately - I get the right data in both cases.

However, in the third query (in the image above), when I try join the two tables, I get a completely incorrect value for column Qty_Shipped. The second query gives Qty_Shipped as 4338 but when I do the join this jumps up to 78084.

Both the first query and second query generate just one row (with the two having identical values for Stock_Tracker).

This is the third query's result:

I am struggling to understand what's causing the issue here.

For each stock_tracker, you are joining every stock_tracker_max row with their tbl_shipment_data_final sum. So, with eighteen stock_tracker_max rows for the stock_tracker in your example, you'll get the shipment sum eighteen times. By aggregating again down to one row for the stock_tracker, the resulting sum is hence eighteen times as big as the original sum.

Instead aggregate both tables per stock_tracker first and then join the results.

SELECT
  m.stock_tracker,
  m.sum_qty_invoiced,
  m.sum_qty_received,
  f.sum_qty_shipped
FROM
(
  SELECT 
    stock_tracker,
    SUM(qty_invoiced) AS sum_qty_invoiced,
    SUM(qty_received) AS sum_qty_received
  FROM `strange-bird-366417.financial_data.stock_tracker_max`
  WHERE stock_tracker = 'WK06 GABONA'
  GROUP BY stock_tracker
) m
LEFT JOIN
(
  SELECT 
    stock_tracker,
    SUM(qty_shipped) AS sum_qty_shipped
  FROM `strange-bird-366417.financial_data.tbl_shipment_data_final`
  WHERE stock_tracker = 'WK06 GABONA'
  GROUP BY stock_tracker
) f ON f.stock_tracker = m.stock_tracker
ORDER BY m.stock_tracker;

Explaining your query in detail

Here is why your query does not work. Let's say you have this data:

and

Now you aggregate your second table thus:

Then you join this result to the first table:

Now you aggregate again and build the final sums:

You have multiplied the second table's sum by the number of matching rows in the first table.