PostgreSQL Join on Unnested Integer Array Column – Count Occurrences

arraycountjoin;postgresql

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.

Best Answer

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).

Related Question