Mysql: how to retrieve data based COUNT() result

I have confused how to retrieve some data from M开发者_高级运维ySQL database. I have the query like:

inspection_report table:
 - Inspection_datetime
 - Line
 - Model
 - Lot_no
 - Serial_number
 - Shift
 - Range_sampling
 - Accesories
 - Acc_qty
Employee table:
 - NIK
 - name

SELECT DATE(A.Inspection_datetime) AS Date, A.Line,TRIM(A.Model) AS Model, A.Lot_no,
       COUNT(A.Serial_number) AS Qty,GROUP_CONCAT(DISTINCT(A.Shift)) AS Shift,
       IF(RIGHT(A.Range_sampling,4)='0000',10000,RIGHT(A.Range_sampling,4))-MID(A.Range_sampling,5,4)+1 AS Merchandise,
       A.Accesories,A.Acc_qty,A.Range_sampling,B.name
FROM inspection_report A
LEFT JOIN Employee B
ON A.NIK=B.NIK
WHERE CHAR_LENGTH(A.Range_sampling) = 17 AND A.Issue='' AND A.Check='' AND A.Approve=''  
GROUP BY A.Line, A.Model, A.Lot_no, A.Range_sampling
ORDER BY Date DESC,A.Line

I want to show data from COUNT(A.Serial_number) AS Qty which have result like:

• Qty = 20
• Qty = 8
• Qty = 32

How do I do to retrieve that?

result like:

|Date      |Line   |Model |Lot_no| Qty|Shift|Merchandise|Accesories|Acc_qty|Range_sampling|name |
+----------+-------+------+------+----+-----+-----------+----------+-------+--------------+-----+
|2011-05-12| fa 02 |BlaBla|021A  | 20 |  A  |  200      |  OK      |11     |   1-200      |tom  |
|2011-05-12| fa 15 | foo  |021A  | 8  |  A  |  200      |  OK      |11     |   1-200      |Bill |
|2011-05-12| fa 01 | Boom |021A  | 32 |  A  |  200      |  OK      |11     |   1-200      | Jim | 

After you do a GROUP BY, you can use HAVING to put conditions on which aggregate data to keep:

SELECT DATE(A.Inspection_datetime) AS Date
     , A.Line
     , TRIM(A.Model) AS Model
     , A.Lot_no
     , COUNT(A.Serial_number) AS Qty
     , GROUP_CONCAT(DISTINCT(A.Shift)) AS Shift
     ,   IF( RIGHT(A.Range_sampling,4)='0000'
           , 10000
           , RIGHT(A.Range_sampling,4)
           )
       - MID( A.Range_sampling, 5, 4)
       + 1
       AS Merchandise
     , A.Accesories
     , A.Acc_qty
     , A.Range_sampling
     , B.name
FROM inspection_report A
    LEFT JOIN Employee B
       ON A.NIK=B.NIK
WHERE CHAR_LENGTH(A.Range_sampling) = 17 
  AND A.Issue='' 
  AND A.Check='' 
  AND A.Approve=''  
GROUP BY A.Line
       , A.Model
       , A.Lot_no
       , A.Range_sampling
HAVING COUNT(A.Serial_number) IN (20, 8, 32)
ORDER BY Date DESC
       , A.Line

It's best not to use LIKE with numerical fields or computed results. This condition:

Qty LIKE '20' OR Qty LIKE '8' OR Qty LIKE '32'

can be written when Qty is an integer as:

Qty = 20 OR Qty = 8 OR Qty = 32

or equivalently as:

Qty IN (20, 8, 32)

So far, i have tried by own self use SELECT inside SELECT:

SELECT X.Date,X.Line,X.Model,X.Qty,...........
FROM(
      SELECT DATE(A.Inspection_datetime) AS Date, A.Line,TRIM(A.Model) AS Model, A.Lot_no,
             COUNT(A.Serial_number) AS Qty,GROUP_CONCAT(DISTINCT(A.Shift)) AS Shift,
             IF(RIGHT(A.Range_sampling,4)='0000',10000,RIGHT(A.Range_sampling,4))-MID(A.Range_sampling,5,4)+1 AS Merchandise,
             A.Accesories,A.Acc_qty,A.Range_sampling,B.name
      FROM inspection_report A
      LEFT JOIN Employee B
      ON A.NIK=B.NIK
      WHERE CHAR_LENGTH(A.Range_sampling) = 17 
      AND A.Issue='' AND A.Check='' AND A.Approve=''  
      GROUP BY A.Line, A.Model, A.Lot_no, A.Range_sampling
      ORDER BY Date DESC,A.Line) X
WHERE X.Qty LIKE '20' OR X.Qty LIKE '8' OR X.Qty LIKE '32'

After that i get what I want. OR USE HAVING (the simplest way).