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;