Skip to content
Advertisement

count total no of people in school

database for school their are two main table student and teacher the creation of table is almost like this

create table students (
    students_id int,
    year int
)


create table Teacher (
    Teacher_id int,
    year int
)

but i what to count total number of people(student + teacher) in each year.

example

students
    students_id     year
             01     2020
             02     2019
             03     2020
             04     2019

Teacher
    Teacher_id      year
            01      2020
            02      2018
            03      2020
            04      2019


answer
    year    total
    2020        4 (2 student + 2 Teacher)
    2019        3 (2 student + 1 Teacher)
    2018        1 (0 student + 1 Teacher)

Advertisement

Answer

Use union all and aggregation:

select year, count(*)
from ((select year from students) union all
      (select year from teachers)
     ) st
group by year;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement