Bdtjtk

I have a table called users, which has the following columns:

id: INT NOT NULL

face: face_type

face_type is an ENUM type that has the following values: 'square', 'round' and 'triangle'.

And I have another table called houses, which has the following columns:

id: INT NOT NULL

user_id: INT NOT NULL

Now, I want to get all the houses grouped by the different type of face types. So, what I have so far is this:

SELECT users.face_type, COUNT(*)

FROM users

LEFT JOIN houses ON houses.user_id = users.id

GROUP BY users.face_type

The problem is that I also want to get rows for face_type which none of the users have, as well as a result for NULL face_type. So, for example, if I have the following data:

users (id, face_type)



1, 'round'

2, 'triangle'



houses (id, user_id)

1, 1

2, 1

3, 2

I would expect the result to be:

face_type, count

'round'          2

'triangle'       1

'square'         0

null             0

I know how to get all the potential values of the face_type ENUM, by doing :

SELECT unnest(enum_range(NULL::face_type)) AS face_types;

But I don't know how to use that to count all potential face types in the aggregate, as well as also calculating for NULL face types.

You can use LEFT JOIN:

SELECT ft.face_type, COUNT(h.user_id)

FROM (SELECT unnest(enum_range(NULL::face_type)) AS face_types

     ) ft LEFT JOIN

     users u

     ON u.face_type = ft.face_type LEFT JOIN

     houses h

     ON h.user_id = u.id

GROUP BY ft.face_type;

To get NULL, just use UNION ALL:

SELECT ft.face_type, COUNT(h.user_id)

FROM (SELECT unnest(enum_range(NULL::face_type)) AS face_types

      UNION ALL

      SELECT NULL

     ) ft LEFT JOIN

     users u

     ON u.face_type = ft.face_type LEFT JOIN

     houses h

     ON h.user_id = u.id

GROUP BY ft.face_type;

Of course, the = will not every match. If that is possible, then you want to change the JOIN condition to u.face_type is not distinct from ft.face_type.

A LEFT JOIN starting from the ENUM and going to users and houses will allow you to recover totals for each enumerated value. To also display the NULL face types, you can use a UNION query.

SELECT 

    ft.face_type,

    COUNT(ho.user_id) as cnt

FROM 

    (SELECT unnest(enum_range(NULL::face_type)) AS face_types) ft

    LEFT JOIN users us ON us.face_type = ft.fact_type

    LEFT JOIN houses ho ON ho.user_id = us.id

GROUP BY ft.face_type

UNION

SELECT

    null,

    COUNT(ho.user_id)

FROM houses ho

INNER JOIN users us ON ho.user_id = us.id AND us.face_type IS NULL

ORDER BY cnt desc

to COUNT(houses.*)

SELECT face_type.type, COUNT(houses.*)

FROM (SELECT unnest(enum_range(NULL::face_type))) AS face_type(type)

FULL JOIN users ON users.face_type=face_type.type

LEFT JOIN houses ON houses.user_id = users.id

GROUP BY face_type.type