homepage Welcome to WebmasterWorld Guest from 54.237.99.131
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

5+ Year Member



 
Msg#: 3591201 posted 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

10+ Year Member



 
Msg#: 3591201 posted 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

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



 
Msg#: 3591201 posted 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