StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL query for items where average price is less than X?
text
Body
copied!<p>I'm stumped with how to do the following purely in MySQL, and I've resorted to taking my result set and manipulating it in ruby afterwards, which doesn't seem ideal. </p> <p>Here's the question. With a dataset of 'items' like:</p> <pre><code>id state_id price issue_date listed 1 5 450 2011 1 1 5 455 2011 1 1 5 490 2011 1 1 5 510 2012 0 1 5 525 2012 1 ... </code></pre> <p>I'm trying to get something <em>like</em>: </p> <pre><code>SELECT * FROM items WHERE ([some conditions], e.g. issue_date >= 2011 and listed=1) AND state_id = 5 GROUP BY id HAVING AVG(price) <= 500 ORDER BY price DESC LIMIT 25 </code></pre> <p>Essentially I want to grab a "group" of items whose average price fall under a certain threshold. I know that my above example "group by" and "having" are not correct since it's just going to give the <code>AVG(price)</code> of that one item, which doesn't really make sense. I'm just trying to illustrate my desired result. </p> <p>The important thing here is I want <em>all</em> of the individual items in my result set, I don't just want to see one row with the average price, total, etc. </p> <p>Currently I'm just doing the above query without the <code>HAVING AVG(price)</code> and adding up the individual items one-by-one (in ruby) until I reach the desired average. It would be really great if I could figure out how to do this in SQL. Using subqueries or something clever like joining the table onto itself are certainly acceptable solutions if they work well! Thanks!</p> <p><b>UPDATE:</b> In response to Tudor's answer below, here are some clarifications. There is always going to be a target quantity in addition to the target average. And we would always sort the results by price low to high, and by date.</p> <p>So if we did have 10 items that were all priced at $5 and we wanted to find 5 items with an average < $6, we'd simply return the first 5 items. We wouldn't return the first one only, and we wouldn't return the first 3 grouped with the last 2. That's essentially how my code in ruby is working right now.</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