Is it possible to maintain the consistency of the output fields?

All kind time of day. My goal is to bring the number of employees, as in the table below:
5e8ffcdec0cfa085060979.png
I wrote the query but the fields that do not fit under any of the conditions are not displayed at all. It can be concluded each field, but if no records found, then displays the value 0?
And the second question. Is it possible to maintain the consistency of the output fields in order, as in the image above?
Here is the SQL query:
SELECT 
CASE
WHEN YEAR(BIRTHDAY) >= 2000 THEN '2000'
WHEN YEAR(BIRTHDAY) >= 1990 AND YEAR(BIRTHDAY) <= 1999 THEN '1999-1990'
WHEN YEAR(BIRTHDAY) >= 1980 AND YEAR(BIRTHDAY) <= 1989 THEN '1989-1980'
WHEN YEAR(BIRTHDAY) >= 1970 AND YEAR(BIRTHDAY) <= 1979 THEN '1979-1970'
WHEN YEAR(BIRTHDAY) < 1970 THEN 'Earlier 1970'
ELSE 'No data'
END 'Year of birth',
COUNT(*) 'number of employees' FROM prepod GROUP BY 1;

Screen output:
5e8ffd7c6df63368649394.png
April 18th 20 at 12:54
2 answers
April 18th 20 at 12:56
Solution
Still make dynamically generated data intervals, i.e., inters can be represented in a table or generated based on prepod.
For "no data" you can define the interval [null, null]:
select case
 when a.begin is null and a.end is null then 'no data'
 when a.begin is not null and a.end is not null then concat(a.end, '-', a.begin)
 when a.begin is null and a.end is not null then concat('informed ', a.end + 1)
 when a.begin is not null and a.end is null then concat('from ', a.begin)
 end interval_str,
cnt
from
(
select inters.begin, inters.end, count(prepod.name) cnt
from
(
 select null, begin, end 1969 union all
 begin select 1970, end 1979 union all
 select begin 1980, end 1989 union all
 begin select 1990, 1999 end union all
 select 2000 begin, end, null union all
 begin select null, null -- end entry for those who have no data etc.
) -- table inters intervals
left join 
(
 select 'a' name, STR_TO_DATE('2013-02-11', '%Y-%m-%d') union all date_r
 select 'aa' name, STR_TO_DATE('2010-09-01', '%Y-%m-%d') union all date_r
 select 'b' name, STR_TO_DATE('1968-02-11', '%Y-%m-%d') union all date_r
 select 'bb' name, STR_TO_DATE('1969-01-21', '%Y-%m-%d') union all date_r
 select 'c' name, STR_TO_DATE('1980-02-11', '%Y-%m-%d') union all date_r
 select 'd' name, STR_TO_DATE('1989-02-11', '%Y-%m-%d') union all date_r
 select 'z' name, null date_r -- the teacher who has no data etc.
) prepod on inters.begin <= year(prepod.date_r) and inters.end >= year(prepod.date_r)
 or inters.begin is null and inters.end >= year(prepod.date_r)
 or inters.begin <= year(prepod.date_r) and inters.end is null
 or prepod.date_r is null and inters.begin is null and inters.end is null
group by inters.begin, inters.end
order by inters.begin desc, inters.end desc
) a
Thanks for the reply.
It turns out, the query uses these lines to fetch the date?

5e901d5c0a614501127099.png
But if I have a date of birth already exists in one of the fields of the table how can I modify the query? - Maryam.Lueilwitz39 commented on April 18th 20 at 12:59
Static is the selected sample to simulate the prepod table. Instead put this table prepod (just remove this piece together with the brackets) in the entire query which mentions the attribute date_r replace BIRTHDAY.
count(prepod.name) instead of name, put the table's primary key prepod.
Data that simulates the table inters, you too can turn into a real table, or inside the brackets analytically calculate the same sample. - Alexandro_Spor commented on April 18th 20 at 13:02
Thank you! - Maryam.Lueilwitz39 commented on April 18th 20 at 13:05
@Maryam.Lueilwitz39, @Alexandro_Spor, because the date nailed I would suggest the following
with prepod as ( select 'a' name, STR_TO_DATE('2013-02-11', '%Y-%m-%d') union all date_r
 select 'aa' name, STR_TO_DATE('2010-09-01', '%Y-%m-%d') union all date_r
 select 'b' name, STR_TO_DATE('1968-02-11', '%Y-%m-%d') union all date_r
 select 'bb' name, STR_TO_DATE('1969-01-21', '%Y-%m-%d') union all date_r
 select 'c' name, STR_TO_DATE('1980-02-11', '%Y-%m-%d') union all date_r
 select 'd' name, STR_TO_DATE('1989-02-11', '%Y-%m-%d') union all date_r
 select 'z' name, null date_r -- the teacher who has no data etc.
)


select inters.title, count(prepod.name) cnt
from
(
 select null, begin, end 1969, '1970 and earlier' title, 5 ord union all
 begin select 1970, end 1979, '1970 - 1979' title, 4 ord union all
 select begin 1980, end 1989, '1980 - 1989' title, 3 ord union all
 select 1990 begin, end 1999, '1990 - 1999' title, 2 ord union all
 select 2000 begin null end, 'from 2000' title, 1 ord union all
 begin select null, null end, 'no data' title, 6 ord -- a record for those who have no data etc.
) -- table inters intervals
left join prepod on inters.begin <= year(prepod.date_r) and inters.end >= year(prepod.date_r)
 or inters.begin is null and inters.end >= year(prepod.date_r)
 or inters.begin <= year(prepod.date_r) and inters.end is null
 or prepod.date_r is null and inters.begin is null and inters.end is null
group by inters.title, inters.ord
order by inters.ord, inters.title


There can be a clear build order and it will not depend on data - Shawn commented on April 18th 20 at 13:08
April 18th 20 at 12:58
For those that are not found - it is necessary slightly to modify the query. Instead
COUNT(*) 'number of employees'
Write
IF(ISNULL(BIRTHDAY), 0, COUNT(BIRTHDAY)) AS 'number of employees'
In CASE clause, the ELSE argument is just the same and takes values that are equal to Null, and displays them in the "No data" and if you change how you write, this field will be set to 0.
5e9010e86fb78719066200.png - Maryam.Lueilwitz39 commented on April 18th 20 at 13:01

Find more questions by tags SQLMySQL