How to use COUNT in my sql query

Question

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

can you please post some example data of your actual and expected result? — manurajhada
– manurajhada, Commented Oct 31, 2012 at 12:06

t-clausen.dk · Accepted Answer · 2012-10-31 12:06:34Z

1

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

answered Oct 31, 2012 at 12:06

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use COUNT in my sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related