semi-complex sql problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

semi-complex sql problem

Post by Charles256 »

Or it could be really easy...dunno..
Either way,
I have a database with product ID's. I want to select the most commonly x occuring instances of a paticular ID. where x is say, the top 10 most occuring and then in another table i have the the product cost and I only need to select the most commonly occuring under a certian price. how would I do this with sql? it seems like something there'd be a built in function for. any ideas?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Sounds like an INNER JOIN query, possibly a subquery.
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

okay.follow that part.say i had a table with product id as a column and the column contained 1,1,1,1,2,2,2,3,3,3,3,3,3 how would I figure out which numbers occurs the most with SQL?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

a GROUP BY clause with one of the aggregate functions, probably COUNT().
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

this worked for me..

Code: Select all

SELECT DISTINCT order_items.product_id, products.id, COUNT( products.id ) , products.price
FROM order_items, products
WHERE products.price <=50
GROUP BY order_items.product_id
ORDER BY COUNT( products.id ) DESC
LIMIT 50
Post Reply