SHARE
TWEET

Untitled

a guest Jun 18th, 2019 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60));
  2.      
  3. CREATE TABLE training_modules (id SERIAL, course VARCHAR(60), teachers_id INTEGER[]);
  4.      
  5. teacher_name  id_count
  6. bob teacher   4
  7. sally lady    3
  8. jimbo jones   5
  9.      
  10. SELECT tid, count(*) as id_count FROM training_modules tm, unnest(tm.teachers_id) as tid GROUP BY tid;
  11.      
  12. 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;
  13.      
  14. id |     t_name      | tc
  15. ----+-----------------+----
  16.   5 | Jimbo Jones     |  5
  17.   4 | Frank McGee     |  4
  18.   6 | Sara Sarason    |  6
  19.   2 | Joshua Jesps    |  2
  20.   1 | Larry Bucatin   |  1
  21.   3 | Natalie Fatali  |  3
  22.      
  23. SELECT t.id, concat_ws(' ', t.first_name, t.last_name) AS teacher_name, tm.id_count
  24. FROM  (
  25.    SELECT unnest(tm.teachers_id) AS id, count(*) AS id_count
  26.    FROM   training_modules tm
  27.    GROUP  BY 1
  28.    ) AS tm
  29. JOIN   teachers t USING (id);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top