Count how much markets have some product

Question

The goal

Count how much markets have some product.

The problem

The query is returning me just the first result and I want the inMarketsQuantity of each product.

The scenario

This is the query:

Select product.Name As productName, 
       Count(marketProducts.ProductId) As inMarketsQuantity
From products As product
Join market_products As marketProducts On product.Id = marketProducts.ProductId
Join markets As market On marketProducts.MarketId = market.Id

This is the result:

+---------------+-------------------+
| productName   | inMarketsQuantity |
+---------------+-------------------+
| Playstation 3 | 15                |
+---------------+-------------------+

And this is what I'm expecting:

+---------------+-------------------+
| productName   | inMarketsQuantity |
+---------------+-------------------+
| Playstation 3 | 10                |
+---------------+-------------------+
| Xbox          | 5                 |
+---------------+-------------------+

What I've already tried

I've already tried the following query, but I'm getting a SQL Error (1064):

Select product.Name As productName, 
       Distinct(Count(marketProducts.ProductId)) As inMarketsQuantity

[...]

Answer 1

Try with group by ProductId:

Select product.Name As productName,
    Count(marketProducts.ProductId) As inMarketsQuantity
From products As product
    Join market_products As marketProducts On product.Id = marketProducts.ProductId
    Join markets As market On marketProducts.MarketId = market.Id
GROUP BY marketProducts.ProductId