Need help to SUM data in MySQL

I need your help to solve my problem. I want to SUM some data which have the same condition.

This my data:

Line    Model    Serial   Lot_no      Range
1       BO       0001     001A        096x0001-096x0100
1       BO       0002     001A        096x0001-096x0100
1       BO       0101     001A        096x0101-096x0200
1       BO       0202     001A        096X0201-096X0203

I want to make some table information from the data above, then I'm use this query:

                    SELECT A.Line, A.Model,
                           A.Lot_no,B.Lot_Quantity,
                           IF(RIGHT(A.Range_sampling,4)='0000',10000,
                           RIGHT(A.Range_sampling,4))-MID(Range_sampling,5,4)+1 
                           AS Merchandise
                    FROM inspection_report A
                    LEFT JOIN prod_sch B
                    ON A.Line= B.Line_Name AND A.Model = B.Model_Code 
                    AND A.Lot_no= CONCAT(LPAD(CAST(B.Lot_No_ AS CHAR),3,'0'),'A')
                    GROUP BY A.Line, A.Model,A.Range_sampling

Then I get the result Like:

Line    Model     LOt_no     Lot_Quantity    Merchandise
1       开发者_StackOverflow社区BO        001A       300             100   //from 096X0001-096X0100
1       BO        001A       300             100   //from 096X0101-096X0200
1       BO        001A       300             3     //from 096X0201-096X0203

How should I do to make the result like:

Line    Model      Lot_no      Lot_Quantity     Merchandise
1       BO         001A        300              203

---

Looks like you want to aggregate the data, so you'd need to group by everything that's not being summed. You'll also need to remove the A.Range_sampling and replace it with any derivative fields you want to group by. IIRC correctly, MySQL will not let you use the alias in the group by.

SELECT  Line,Model,Lot_no, Lot_Quantity, SUM(Merchandise) FROM (
                SELECT A.Line, A.Model,
                       A.Lot_no,B.Lot_Quantity,
                       IF(RIGHT(A.Range_sampling,4)='0000',10000,
                       RIGHT(A.Range_sampling,4))-MID(Range_sampling,5,4)+1 
                       AS Merchandise
                FROM inspection_report A
                LEFT JOIN prod_sch B
                ON A.Line= B.Line_Name AND A.Model = B.Model_Code 
                AND A.Lot_no= CONCAT(LPAD(CAST(B.Lot_No_ AS CHAR),3,'0'),'A')

          )
          GROUP BY Line,Model,Lot_no, Lot_Quantity

EDIT This might be a little cleaner