How to select categories and count number of products of each category?

Question

How to select categories and count number of products of each category?

Navigation

#1 by (9 votes)

6

I'm trying to select the categories of the database and count in the same query the number of products registered in that category. So far so good, the problem is that I can not return the category when there is no product registered, for example:

SELECT t1.id,t1.nome,t1.imagem,COUNT(*) AS Quantidade_produtos 
FROM categorias_produtos AS t1 
INNER JOIN produtos AS t2 ON t2.ref=t1.id 
GROUP BY t1.id

I have the categories:

categoria1
categoria2
categoria3

Products:

produto1 -> categoria1
produto2 -> categoria1
produto3 -> categoria2

I would like the result of the query to look something like this:

ID |   Nome   | Quantidade_produtos
 1 |categoria1|         2
 2 |categoria2|         1
 3 |categoria3|         0

But the query does not return this:

ID |   Nome   | Quantidade_produtos
 1 |categoria1|         2
 2 |categoria2|         1

You are ignoring the categories that do not have any products registered:

 3 |categoria3|         0

Any tips for listing all categories with the amount of products?

mysql sql

asked by anonymous 05.05.2014 / 22:09

1 answer

Manipulate price in inventory control What is the name of the modeling / code notation?

score 9 · Accepted Answer

Instead of INNER JOIN with the LEFT JOIN product table:

SELECT t1.id,t1.nome,t1.imagem,COUNT(t2.*) AS Quantidade_produtos
FROM categorias_produtos AS t1
LEFT JOIN produtos AS t2 ON t2.ref=t1.id GROUP BY t1.id

For LEFT JOIN it will take everything as long as it contains the record in the left table, already in the inner it would have to have the id in the two tables so it can return a result for that row and when it does not have the record in the table of products, it will ignore this line if inner join is used, so the use of left join is the correct