Forum Moderators: open
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.
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 )
