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;

Answer 1

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.