sum of count in where clause (or having) in the same table with group by I have a products table with columns id, binno, name, sku, inwarddate and more.

Each bin stores 20 or 30 products. We need to group by binno for dispatch and show sum of items in each bin. That makes the query look like: 

SELECT  count(*) as total,binno, model, color, sku FROM products
 where location=1 and sku='4'
 GROUP BY binno


This is working. It gives the following result:

Binno model color sku total
231    3    2      4   20
232    3    2      4   20
...


Now I need to select bins where total of this count=500 (or any other number) to limit the results to show 25 bins. Required number may not be 500. it may be any number but the user knows the no of items in a bin (just like ATM user).

I need sum of count(*) in having or where. That will solve my problem.
First limit the result to 500 (or whatever) and then GROUP BY:

SELECT 
    binno,
    count(*) AS total
FROM 
    ( SELECT binno
      FROM products
      WHERE location = 1 
        AND sku = '4'
      ORDER BY binno             -- the order may be different
      LIMIT 500
    ) AS lim
GROUP BY 
    binno ;


Optimal index for this query:

INDEX (location, sku, binno)