SQL: Order by count return only the first row-CodePudding

I have two tables products and product_views.

I want to write a query that returns the products with the largest number of views.

products table has id and name columns. product_views has columns id, customer_id, product_id. The counting is based on the product_id column.

Here is my query:

SELECT * 
FROM products
LEFT JOIN product_views ON products.id = product_views.product_id
ORDER BY COUNT(product_views.product_id) DESC;

The problem is it returns only the first product. I think the issue is that initially the product_views table is all empty (all values are null).

How to solve this issue?

CodePudding user response：

Based on your criteria, the following should work -

SELECT
 p.id,
 p.name,
 count(v.product_id) as views
FROM products p
LEFT JOIN product_views v
 ON p.id = v.product_id
 GROUP BY 1,2
ORDER BY views DESC;

The query ignores customer_id, and counts product_ids based on the id/name groupings.

CodePudding user response：

Calculates count in separate script:

SELECT products.id, products.name, pv.count FROM products
JOIN (SELECT product_id, COUNT(*) as count FROM product_views GROUP BY product_id)
pv ON (products.id = product_id )
ORDER BY pv.count DESC;

Example: SQL Fiddle