CODEWITHSUNDEEP
mysqlcount
Sergey Ovchinnik
Sergey Ovchinnik

Reputation: 502

How many times each value from a list appears in a column in another table. MYSQL

I have a table of id values and need to count how many times each of those ids appears in a column in another table.

I have figured out how to do it only for values that appear at least once:

SELECT one.id, COUNT(*) FROM table1 one, table2 two WHERE one.id = two.id GROUP BY one.id;

but can't figure out how to also include ids that appear in first but don't appear in second table at all.

Example: 

table1:       table2:

+-----+      +-----+
| id  |      | id  |
+-----+      +-----+
| 11  |      | 11  |
| 12  |      | 12  |
| 13  |      | 14  |
| 14  |      | 11  |
+-----+      | 11  |
             | 12  |
             +-----+

The result would be:

+-----+----------+
| id  | count(*) |
+-----+----------+
| 11  | 3        |
| 12  | 2        |
| 14  | 1        |
+-----+----------+

I'm trying to make it also include line | 13 | 0 |

Upvotes: 2

Views: 472

Answers (1)

SMA
SMA

Reputation: 37023

You are doing an implicit inner join which is discouraged. Instead what you need is a left join, something like:

SELECT one.id, COUNT(two.id) 
FROM table1 one LEFT JOIN table2 two 
ON one.id = two.id 
GROUP BY one.id;

Upvotes: 1

Related Questions