Join on unnested integer array column and count occurrences in array

Question

I am trying to join two tables after I have unnested one of them. The tables are:

CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60));

CREATE TABLE training_modules (id SERIAL, course VARCHAR(60), teachers_id INTEGER[]);

I want to write a query that will return the teachers names and the number of training_modules they've participated in (a count of their IDs), like so:

teacher_name id_count
bob teacher 4
sally lady 3
jimbo jones 5

So far, I've been able to get a count of each ID with this query:

SELECT tid, count(*) as id_count
FROM training_modules tm, unnest(tm.teachers_id) as tid
GROUP BY tid;

All fine and good. I tried to apply that to another query, where I join the teachers table, and it doesn't work as expected. This query:

SELECT t.id, concat(t.first_name, ' ', t.last_name) AS teacher_name, tm.id_count
FROM (
 SELECT unnest(training_modules.teachers_id) AS id_count, count(*)
 FROM training_modules
 GROUP BY id_count
 ) AS tm
INNER JOIN teachers t ON tm.id_count = t.id;

Results in the table below, where the id_count column is no longer a count, but returns the same value as the t.id column.

 id | t_name | tc 
----+-----------------+----
 5 | Jimbo Jones | 5
 4 | Frank McGee | 4
 6 | Sara Sarason | 6
 2 | Joshua Jesps | 2
 1 | Larry Bucatin | 1
 3 | Natalie Fatali | 3

Any help to get the right count appearing while joining the tables would be much appreciated.

Answer 1

Basically, you attached the column alias to the wrong expression in your 2nd query.

SELECT id, concat_ws(' ', t.first_name, t.last_name) AS teacher_name, tm.id_count
FROM (
 SELECT unnest(tm.teachers_id) AS id, count(*) AS id_count
 FROM training_modules tm
 GROUP BY 1
 ) AS tm
JOIN teachers t USING (id);

Aside:
concat_ws(' ', t.first_name, t.last_name) is typically better, as it only adds the separator where it makes sense (when both first_name & last_name are NOT NULL).

• Joshua Swiss: Thanks! For both the solution and the tip! Much appreciated!