How to make LEFT JOIN work with grouping/count in MySQL?

Question

I have a query like this:

SELECT type.id, type.name, COUNT(*) AS tot
FROM page
LEFT JOIN type ON page.type=type.id
GROUP BY type.id

However, this doesn't select all the types: it misses out any types that are not in the page table yet. I just want it to list every type with a number representing how many pages have that type, including 0 where the type doesn't occur. Do I need a different join?

Answer 1

USE Coalesce to give NULL a value to GROUP BY

SELECT
    COALESCE(type.id, 0),
    COALESCE(type.name, 'NoType'),
    COUNT(*) AS tot
FROM
    page
LEFT JOIN
    type
ON
    page.type=type.id
GROUP BY
    COALESCE(type.id, 0),
    COALESCE(type.name, 'NoType')

Answer 2

A left join brings back all the rows from the table on the left side of the join expression regardless if they exist on the table on the right side try this (switching the sides of the expression):

SELECT type.id, type.name, COUNT(*) AS tot
FROM  type 
LEFT JOIN  page ON type.id=page.type
GROUP BY type.id, type.name

Answer 3

Do a RIGHT JOIN instead. Try this:

SELECT type.id, type.name, COUNT(page.type) AS tot
FROM page
RIGHT JOIN type ON page.type=type.id
GROUP BY type.id

Note the different way of counting. page.type will be NULL for types that don't have pages. Such rows will be ignored when doing the COUNT

[EDIT] Here is an example. If we have following data:

  type
id  name
 1     A
 2     B
 3     C

  page
id   type
 1     1
 2     1
 3     2

If we do the JOIN like in my answer (no GROUP BY or COUNT), our result set will look like this:

type.id  type.name  page.id  page.type
      1          A        1          1
      1          A        2          1
      2          B        3          2
      3          C     NULL       NULL

Now when we do a GROUP BY and COUNT(type.id), we are counting rows where type.id is not NULL. That would be all rows: result is 2 for A and 1 for B and C.

If we do COUNT(page.type) instead, we get 2 for A, 1 for B and 0 for C (because page.type was NULL for C!)