Combine the output of different queries as a single query output

Question

How do I combine the output of different queries.

I have the following sql queries with the output:

1) select d.departmentid, d.name, count(distinct(sg.personid)) as noOfStudents from department d inner join course c on c.departmentid = d.departmentid inner join student_grade sg on sg.courseid = c.courseid group by d.departmentid, d.name;
+--------------+--------------+--------------+
| departmentid | name         | noofstudents |
+==============+==============+==============+
|          101 | Computer Sci |            1 |
|          104 | Mech         |            2 |
|          103 | EEE          |            1 |
+--------------+--------------+--------------+



2) select d.departmentid, d.name, count(distinct(ci.personid)) as noOfTeachers from department d inner join course c on c.departmentid = d.departmentid inner join course_instructor ci on ci.courseid = c.courseid group by d.departmentid, d.name;
+--------------+--------------+--------------+
| departmentid | name         | noofteachers |
+==============+==============+==============+
|          101 | Computer Sci |            1 |
|          103 | EEE          |            2 |
|          104 | Mech         |            1 |
|          102 | ECE          |            3 |
+--------------+--------------+--------------+

3) select d.departmentid, d.name, count(distinct(c.courseid)) as noOfCourses from department d inner join course c on c.departmentid = d.departmentid group by d.departmentid, d.name;
+--------------+--------------+-------------+
| departmentid | name         | noofcourses |
+==============+==============+=============+
|          101 | Computer Sci |           3 |
|          102 | ECE          |           3 |
|          104 | Mech         |           1 |
|          103 | EEE          |           2 |
+--------------+--------------+-------------+

Now I want to combine all three into a single table to display the data. How can I do that?

Here I am trying UNION operation and is it good to use?

Answer 1

If you need to get Students, Teachers and Courses as separate columns in the output, you can try something like this:

select 
    d.departmentid, d.name, 
    count(distinct(sg.personid)) as noOfStudents, 
    0 as noofteachers, 0 as noofcourses 
from 
    department d 
inner join 
    course c on c.departmentid = d.departmentid 
inner join 
    student_grade sg on sg.courseid = c.courseid 
group by 
    d.departmentid, d.name

UNION ALL

select 
    d.departmentid, d.name, 0 as noOfStudents,
    count(distinct(ci.personid)) as noOfTeachers, 0 as noofcourses 
from 
    department d 
inner join 
    course c on c.departmentid = d.departmentid 
inner join 
    course_instructor ci on ci.courseid = c.courseid 
group by 
    d.departmentid, d.name

UNION ALL

select 
    d.departmentid, d.name, 0 as noOfStudents, 
    0 as noofteachers, count(distinct(c.courseid)) as noOfCourses 
from 
    department d 
inner join 
    course c on c.departmentid = d.departmentid 
group by 
    d.departmentid, d.name

This should produce something like this:

+--------------+--------------+--------------+--------------+--------------+  
| departmentid | name         | noofstudents | noofteachers | noofcourses  |  
+==============+==============+==============+==============+==============+  
|          101 | Computer Sci |             1|             0|             0|  
|          104 | Mech         |             2|             0|             0|  
|          103 | EEE          |             1|             0|             0|  
|          101 | Computer Sci |             0|             1|             0|  
|          103 | EEE          |             0|             2|             0|  
|          104 | Mech         |             0|             1|             0|  
|          102 | ECE          |             0|             3|             0|  
|          101 | Computer Sci |             0|             0|             3|  
|          102 | ECE          |             0|             0|             3|  
|          104 | Mech         |             0|             0|             1|  
|          103 | EEE          |             0|             0|             2|  
+--------------+--------------+--------------+--------------+--------------+  

If you need aggregated values, just enclose the entire query into brackets and sum the results

SELECT 
    departmentid, name, 
    SUM(noOfStudents), SUM(noOfTeachers), SUM(noOfCourses)
FROM 
    (Query1 UNION ALL Query2 UNION ALL Query3) AS mySubQuery
GROUP BY 
    departmentid, name