CODEWITHSUNDEEP
sql
Hisham Safwat
Hisham Safwat

Reputation: 23

SQL returned unexpected result from join clause ,why

you can find the whole scenario in the picture

thanks in advance Source data and expected outcome

My SQL query is as follows:

SELECT 
    a.item_code, 
    a.rep_code,
    sum(t.sales_target),
    sum(a.actual)
FROM sales_target t   
    JOIN  sales_actual a
        ON a.item_code = t.item_code AND a.rep_code = t.rep_code 
GROUP BY 
    a.item_code, 
    a.rep_code

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You are getting a Cartesian product for each item code/rep, so the values are wrong. One method is to use union all and then aggregate:

select ta.item_code, ta.rep_code,
       sum(ta.sales_target), sum(ta.actual)
from ((select item_code, rep_code, sales_target, 0 as actual
       from sales_target t
      ) union all
      (select item_code, rep_code, 0 as sales_target, actual
       from sales_actual a
      )
     ) ta
group by ta.item_code, ta.rep_code;

Upvotes: 2

Related Questions