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!