StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What MySQL does makes perfectly sense. What you want to do does not make sense:</p> <ul> <li><p>if you have let's say 4 items, each with price of 5 and you put <code>HAVING AVERAGE <= 7</code> what you say is that the query should return <strong>ALL</strong> the permutations, like:</p> <ul> <li>{1} - since item with id 1, can be a group by itself</li> <li>{1,2}</li> <li>{1,3}</li> <li>{1,4}</li> <li>{1,2,3}</li> <li>{1,2,4}</li> </ul> <p>... </p> <ul> <li>and so on?</li> </ul></li> </ul> <p>Your algorithm of computing the average in ruby is also not valid, if you have items with values 5, 1, 7, 10 - and seek for an average value of less than 7, element with value 10 can be returned just in a group with element of value 1. But, by your algorithm (if I understood correctly), element with value 1 is returned in the first group.</p> <p><strong>Update</strong></p> <p>What you want is something like the <a href="http://en.wikipedia.org/wiki/Knapsack_problem" rel="nofollow">Knapsack problem</a> and your approach is using some kind of <a href="http://en.wikipedia.org/wiki/Greedy_algorithm" rel="nofollow">Greedy Algorithm</a> to solve it. I don't think there are straight, easy and correct ways to implement that in SQL.</p> <p>After a google search, I found <a href="http://sqlinthewild.co.za/index.php/2011/02/22/and-now-for-a-completely-inappropriate-use-of-sql-server/" rel="nofollow">this article</a> which tries to solve the knapsack problem with AI written in SQL.</p> <p>By considering your item price as a weight, having the number of items and the desired average, you could compute the maximum value that can be entered in the 'knapsack' by multiplying <code>desired_cost</code> with <code>number_of_items</code></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload