A query that will see which results are the most-CodePudding

There is a table where strings are stored by type.
How to write 1 query so that it counts and outputs the number of results of each type.

Tried to do this:

SELECT COUNT(t1.id), COUNT(t2.id) 
FROM table t1 
LEFT JOIN table t2 
ON t2.type='n1' 
WHERE t1.type='b2';

But nothing works.
There can be many types, how can this be done?

CodePudding user response：

I think this is what you want:

SELECT t1.type, COUNT(DISTINCT t1.id), COUNT(DISTINCT t2.id)
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.type = t2.type
GROUP BY t1.type

Note that this won't show any types that are only in table2. For that you'd need a FULL OUTER JOIN, which MySQL doesn't have. See How can I do a FULL OUTER JOIN in MySQL? for how to emulate it.