Error when query count product by category

Question

Category table

+----+------+
| id | name |
+----+------+
|  1 | cat1 |
|  2 | cat2 |
|  3 | cat3 |
+----+------+

Product table

+----+-------+--------+   
| id | name  | cat_id |    
+----+-------+--------+
|  1 | prod1 |      1 |    
|  2 | prod2 |      1 |    
|  3 | prod3 |      3 |    
|  4 | prod4 |      1 |    
|  5 | prod5 |      3 |
|  6 | prod6 |      1 |    
+----+-------+--------+

This is my query:

 SELECT c.id, c.name, count( p.cat_id ) num
    FROM categories c
    INNER JOIN products p ON c.id = p.cat_id
    GROUP BY p.cat_id

This is result:

+----+------+-------+    
| id | name | count |    
+----+------+-------+    
|  1 | cat1 |     4 |    
|  3 | cat3 |     2 |  
+----+------+-------+

Error now show count cat=2 How to fix get this result:

+----+------+-------+    
| id | name | count |    
+----+------+-------+    
|  1 | cat1 |     4 |    
|  2 | cat2 |     0 |    
|  3 | cat3 |     2 |  
+----+------+-------+

Full example here: http://sqlfiddle.com/#!9/f2cbb9/1

score 4 · Answer 1 · answered Feb 29 '16 at 07:50

4

I think, you just have to change your join into a left join.

SELECT c.id, c.name, count( p.cat_id ) num
        FROM categories c
        LEFT JOIN products p ON c.id = p.cat_id
        GROUP BY p.cat_id

answered Feb 29 '16 at 07:50

dns_nx

3,651
4
37
66

score 1 · Answer 2 · edited May 23 '17 at 10:27

1

You need to use left join

 SELECT c.id, c.name, count( p.cat_id ) num
    FROM categories c
    LEFT JOIN products p ON c.id = p.cat_id
    GROUP BY p.cat_id

ref : What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

edited May 23 '17 at 10:27

Community

1
1

answered Feb 29 '16 at 07:50

Boby

1,131
3
20
52

score 0 · Answer 3 · answered Feb 29 '16 at 07:56

0

SELECT c.id, c.name, count( p.cat_id ) num
        FROM categories c
        LEFT JOIN products p ON c.id = p.cat_id
        GROUP BY p.cat_id

answered Feb 29 '16 at 07:56

Mohd Zubair Khan

263
2
16

score 0 · Answer 4 · edited May 23 '17 at 11:50

Every answer is correct that you will need to use a LEFT JOIN to achieve the specified result. To get the exact result you requested, you would need to do this:

SELECT c.id, c.name, COUNT(p.cat_id) AS `count`
FROM categories AS c
LEFT JOIN products AS p ON c.id = p.cat_id
GROUP BY p.cat_id
ORDER BY c.id ASC

The SO question Boby referenced has a good explanation about why you can't use an INNER JOIN to achieve your desired result.

Error when query count product by category

4 Answers4