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;