Welcome to WebmasterWorld Guest from 54.198.240.21

Forum Moderators: open

Message Too Old, No Replies

Need help with MySQL SELECT statement

using GROUP BY, HAVING and ORDER BY

     
8:25 pm on Mar 4, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 15, 2005
posts: 79
votes: 0


I've been fighting with this thing for so long I'm starting to wonder if it possible but it seams like it should be!

We sell a bunch of different products by a bunch of different Artist's, every night the system tallies a sales report and for each item, calculates the avg number of items sold per day over the last 7 days and updates that value in the DB as sellRate.

Now, at any given moment I want to be able to answer the question: "for each individual artist, which item do we have the most inventory of?" Not simply the number in stock, but (inStock/sellRate) which we call daysAvail.

What I have so far:

$query = "SELECT pID, pArtist (pInStock/pSellRate) as daysAvail, count(pArtist) FROM products GROUP BY pArtist HAVING COUNT(pArtist)>0 ORDER BY daysAvail DESC";

Isn't working because while it grabs all the distinct artists, it does it from the start of the database and then orders the results by daysAvail. What I need is to somehow order the database by daysAvail, then go through from the start and grab the distinct artists.

Any help or direction would be greatly appreciated!

William.

3:10 am on Mar 5, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


"for each individual artist, which item do we have the most inventory of?"

The query should look something like this:


SELECT pID, pArtist, (pInStock/pSellRate) AS daysAvail
FROM products AS p1
WHERE (pInStock/pSellRate) = ( SELECT MAX(pInStock/pSellRate)
FROM products
WHERE pArtist=p1.pArtist )
5:02 am on Mar 5, 2008 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:10551
votes: 10


maybe this will do what you want:
$query = "SELECT pID, pArtist (pInStock/pSellRate) as daysAvail, count(pArtist) FROM products WHERE MAX(daysAvail) GROUP BY pArtist, daysAvail HAVING COUNT(pArtist)>0 ORDER BY daysAvail DESC";