CODEWITHSUNDEEP
sqlcount
Tom-pouce
Tom-pouce

Reputation: 788

How to use COUNT in my sql query

I'm trying to count the number of occurrence of product_id. This is my query that's return 10 products but not according the number of occurrence of product_id.

SELECT name, category_id, product_id 
FROM product, notification 
WHERE type='product_consumed' OR type='product_rate' AND product.id = notification.product_id AND category_id="1" 
GROUP BY product_id ORDER BY product.category_id ASC LIMIT 10

I tried to COUNT the occurrenceof product_id like this, but it returns to me bad results,

SELECT name, category_id, product_id, COUNT(*) AS most_viewed 
FROM product, notification 
WHERE type='product_consumed' OR type='product_rate' AND product.id = notification.product_id AND category_id=".$cat." 
GROUP BY product_id ORDER BY product.category_id ASC, most_viewed DESC LIMIT 10

My wish is to have a sql response like this :

Category of the product | Name of product | Number of views

Thanks

Upvotes: 1

Views: 848

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try this, you are missing a set of brackets

SELECT name, category_id, product_id, COUNT(*) AS most_viewed 
FROM product, notification 
WHERE (type='product_consumed' OR type='product_rate') AND product.id = notification.product_id AND category_id=".$cat." 
GROUP BY product_id 
ORDER BY product.category_id ASC, most_viewed DESC LIMIT 10

Upvotes: 1

Related Questions