Welcome to WebmasterWorld Guest from 54.227.1.130

Forum Moderators: open

Message Too Old, No Replies

Need help with MySQL SELECT statement

using GROUP BY, HAVING and ORDER BY

     

wsmeyer

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

5+ Year Member



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.

syber

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

10+ Year Member



"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 )

phranque

5:02 am on Mar 5, 2008 (gmt 0)

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



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";
 

Featured Threads

Hot Threads This Week

Hot Threads This Month