How to SUM in LEFT JOIN

Question

I have some data from two tables:

Table prod_schedule
Line      Model      Lot_no      Quantity    Lot_Quantity
FA 01     KD-R330JD  060A        240         1000
FA 01     KD-R330JD  060A        260         1000
FA 02     KD-R330JD  060A        400         1000
FA 02     KD-R330JD  060A        100         1000

Table inspection_report
Line      Model      Lot_no      Merchandise
FA 01     KD-R330JD  060A        200        
FA 01     KD-R330JD  060A        300         
FA 02     KD-R330JD  060A        500         

I want to show data like:

Line      Model      Lot_no      Merchandise   Quantity
FA 01     KD-R330JD  060A        500           500
FA 02     KD-R330JD  060A        500           500

And I try some query like:

SELECT A.Line, TRIM(A.Model) AS Model,A.Lot_no,B.Quantity,A.Merchandise
FROM inspection_report A
LEFT JOIN prod_schedule B
ON A.Line= B.Line_Name AND CONCAT('%',B.Model_Code,'%') LIKE CONCAT('%',A.Model,'%') 
                        AND A.Lot_no=B.Lot_No_
WHERE A.Model LIKE '%330%' AND A.Lot_no LIKE '%060%'
GROUP BY A.Line,A.Model,A.Lot_no,Merchandise

But I get result like :

Line    Model     Lot_no    Merchandise Quantity 
FA 01   KD-R330JD   060A    200          240
FA 02   KD-R330JD   060A    300          240

Answer 1

[ Post Overhaul ]

With the most recent update of the question, here's the code that would generate the desired output:

-- VERSION 01
SELECT t1.Line, t1.Model, t1.Lot_no, t1.Merchandise, t2.Quantity
FROM
(
    SELECT 
          A.Line, TRIM(A.Model) AS Model, A.Lot_no
        , SUM(A.Merchandise) AS Merchandise
    FROM lk_inspection_report AS A
    GROUP BY A.Line, TRIM(A.Model), A.Lot_no
) t1
INNER JOIN
(
    SELECT 
          B.Line, TRIM(B.Model) AS Model, B.Lot_no
        , SUM(B.Quantity) AS Quantity
    FROM lk_prod_schedule AS B
    GROUP BY B.Line, TRIM(B.Model), B.Lot_no
) t2
ON 
        t1.Line = t2.Line 
    AND t1.Model = t2.Model 
    AND t1.Lot_no = t2.Lot_no
WHERE 
        t1.Model LIKE '%330%' 
    AND t1.Lot_no LIKE '%060%'

And this is..

-- VERSION 02
SELECT t1.Line, t1.Model, t1.Lot_no, t1.Merchandise, t2.Quantity
FROM
(
    SELECT 
          A.Line, TRIM(A.Model) AS Model, A.Lot_no
        , SUM(A.Merchandise) AS Merchandise
    FROM lk_inspection_report AS A
    WHERE t1.Model LIKE '%330%' AND t1.Lot_no LIKE '%060%'
    GROUP BY A.Line, TRIM(A.Model), A.Lot_no
) t1
INNER JOIN
(
    SELECT 
          B.Line, TRIM(B.Model) AS Model, B.Lot_no
        , SUM(B.Quantity) AS Quantity
    FROM lk_prod_schedule AS B
    WHERE B.Model LIKE '%330%' AND B.Lot_no LIKE '%060%'
    GROUP BY B.Line, TRIM(B.Model), B.Lot_no
) t2
ON 
        t1.Line = t2.Line 
    AND t1.Model = t2.Model 
    AND t1.Lot_no = t2.Lot_no

I aggregated data from each table and then joined them after the grouping. Version 2 places the WHERE clause in both sub queries; please test which is faster for you.

Answer 2

try like this . code

with a as
    (
    select  Line,Model,Lot_no, sum(Merchandise) as Merchandise  from  inspection_report 
    group by Line,Model,Lot_no

    ),
    b as
    (
    select  Line,Model,Lot_no,SUM(Quantity) as Quantity from   prod_schedule 
    group by Line,Model,Lot_no
    )

    select a.Line,a.Model,a.Lot_no,a.Merchandise ,b.Quantity from a,b where a.Line= b.Line