CODEWITHSUNDEEP
mysqlsql
useyourillusiontoo
useyourillusiontoo

Reputation: 1367

Retrieve most popular products in a category sql statement

I'm trying to write an all encompassing SQL statement to retrieve the most popular items from my shop based on category ID. In english: For categoryID sort the most sold items

I've managed to query tblOrderContents and group + sort the results with most popular at the top:

SELECT productID, count(productID)
FROM tblOrderContents oc
GROUP BY productID 
ORDER BY count(productID) DESC

which produces:

 ID                         COUNT
 16                          419
 12                          52
 34                          38
 33                          33

But I'm struggling to figure out how to retrieve this for only a specific category. I'm thinking it will require a left join along the lines of but this doesn't work:

SELECT productID from tblProdCat 
LEFT JOIN (SELECT STATEMENT DETAILED ABOVE)
WHERE categoryID = '7'

I hope this makes sense. I've been thinking about it so long i'm sure ive missed something obvious. Any advice would be great.

Thanks

Upvotes: 1

Views: 1539

Answers (1)

Michael
Michael

Reputation: 1596

Is productID in tblProdCat? If so, the following statement should work:

SELECT productID, count(productID)
FROM tblOrderContents oc
INNER JOIN tblProdCat pc ON oc.ProductId = pc.ProductId AND pc.CategoryId = '7'
GROUP BY productID 
ORDER BY count(productID) DESC

Explanation: This inner join gives back only rows where the criteria (categoryID = '7' and matching productId's) is met. If a row does not satisfy this criteria, in either table, the row will not be returned.

Upvotes: 2

Related Questions