Page 1 of 1

semi-complex sql problem

Posted: Sun Aug 13, 2006 2:38 pm
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?

Posted: Sun Aug 13, 2006 2:41 pm
by feyd
Sounds like an INNER JOIN query, possibly a subquery.

Posted: Sun Aug 13, 2006 2:43 pm
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?

Posted: Sun Aug 13, 2006 2:47 pm
by feyd
a GROUP BY clause with one of the aggregate functions, probably COUNT().

Posted: Sun Aug 13, 2006 3:18 pm
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