homepage Welcome to WebmasterWorld Guest from 54.196.196.108
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Need help with MySQL SELECT statement
using GROUP BY, HAVING and ORDER BY
wsmeyer




msg:3591203
 8:25 pm on Mar 4, 2008 (gmt 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.

 

syber




msg:3591557
 3:10 am on Mar 5, 2008 (gmt 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 )

phranque




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved