Joining results from many tables in postgresql

Question

I have got a table students:

student_id | name | course
-------------------------------------
  1        | Jack | Comp_Sci
  2        | John | Maths
  3        | Matt | Comp_Sci
  4        | Pete | Biology

Table Departments:

course   | department
-------------------------
Comp_Sci | Comp_and_Math
Maths    | Comp_and_Math
Biology  | Bio_and_Chem

Table using_computers

computer_id | student_id 
-------------------------
1           |  2
2           |  2
2           |  3
2           |  4
3           |  1
4           |  2
4           |  4

And table computers

computer_id | name
---------------------
1           | Apple
2           | Dell
3           | Asus
4           | Acer

And I would like to list all used computers in departments like this

Comp_and_Maths: Apple 1, Dell 2, Asus 1, Acer 1, sum: 5
Bio_and_Chem: Dell 1, Acer 1 , sum:2

I have already written 2 queries but I dont know how to connect them:

SELECT Departments.department, obj_in_class.list_ids
  FROM Departments LEFT JOIN
  (SELECT Departments.course, array_agg(students.student_id) AS list_ids
  FROM Departments
  LEFT JOIN students
      ON Departments.course = students.course GROUP BY Departments.course) AS obj_in_class
   ON Departments.course = obj_in_class.course GROUP BY Departments.department, obj_in_class.list_ids;

(SELECT students.student_id AS id, array_agg(m.name) AS computers
  FROM students LEFT JOIN
    (SELECT computers.name, using_computers.student_id FROM using_computers LEFT JOIN computers ON using_computers.computer_id = computers.computer_id) AS m
    ON students.student_id = m.student_id
      GROUP BY students.student_id) AS students_with_computers;

Yeah, that is really bad for just one query. Let see if someone answer you. I don't have the time right now. The best case would be to solve it in a plain listing results and in the application code (whatever it is) you would format it as you want. — Jorge Campos
– Jorge Campos, Commented Feb 26, 2016 at 12:30

OcuS · Accepted Answer · 2016-02-26 15:41:44Z

2

It does not reuse your previous queries:

SELECT 
     -- aggregate computer name and count as a string
    grouped.department || ': ' || 
    array_to_string(array_agg(grouped.name || ' ' || grouped.count), ', ')
     -- sum all the counts
    || ', sum: ' || sum(grouped.count)
  FROM (
    SELECT
        D.department,
        C.name,
        count(C.name) -- count computers' name per department
      FROM Departments D
      JOIN students S USING (course)
      JOIN using_computers UC USING (student_id)
      JOIN computers C USING (computer_id)
      GROUP BY D.department, C.name
      ORDER BY D.department, C.name
    ) grouped
  GROUP BY grouped.department
;

SQLFiddle

The idea is to join every table and group by department and computer in order to get the count of (brands of) computers for each department. This is done in the grouped sub-select. We now have every data and count we need.

Then we just group by department and aggregate everything.

edited Feb 26, 2016 at 15:41

answered Feb 26, 2016 at 15:37

OcuS

5,3203 gold badges38 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jorge Campos Over a year ago

It was not as hard as I was thinking, forgot about array_agg function actually. Great. +1

Collectives™ on Stack Overflow

Joining results from many tables in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related