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

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.

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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related