0

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.

closed as unclear what you're asking by ypercubeᵀᴹ, joanolo, McNets, Philᵀᴹ, Joe Obbish Apr 29 '17 at 16:28

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • So - for a given sku, every bin will have n items; depending on the sku n will be either 20 or 30. And, when looking for a quantity of a given 'sku', you will always need n * y items - you will always use all items in a bin. If an item is 20 to a bin, you'll never wind up with a bin with 10 items, or 18, or 2. – RDFozz Apr 25 '17 at 17:24
  • So, let's say you need 42 bins of sku 4, and there are 207 bins of sku 4. And, let's assume the bin ID values happen to be 1-207. Does it matter which 42 bins we return? are bins 1-42, bins 166-207, bins 1,4,7,...,124 (every third bin, starting from 1), all equally valid? Is there some other field that would make some bins better choices than others? – RDFozz Apr 25 '17 at 17:29
  • So you're basically trying to solve the Knapsack Problem using SQL? MySQL is not good enough for it, given its weak SQL features. – mustaccio Apr 25 '17 at 18:18
  • actually I need sum of count(*) in having or where. That will solve my problem – Sam Apr 26 '17 at 0:20
2

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)
  • 1
    Optimal: INDEX(location, sku, binno) – Rick James Apr 29 '17 at 5:12
0

You can use:

SELECT   count(*) as total, binno, model, color, sku
FROM     products
WHERE    location=1
AND      sku     ='4'
GROUP BY binno
HAVING   total   =500
  • Thanks for your help, actually count is 20 for every bin. I need to list first 25 bins where total of sum of these count() for these will be 500( 25*20) – Sam Apr 25 '17 at 16:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.