I can't test this because I don't have access to your input_table but the sql equivalent would be something like this:
create table if not exists output_table as
with temp_equivalence as (
select *
from (values
('No Education', 'None or Primary', 1),
('1st Std. Pass', 'None or Primary', 1),
('2nd Std. Pass', 'None or Primary', 1),
('3rd Std. Pass', 'None or Primary', 1),
('4th Std. Pass', 'None or Primary', 1),
('5th Std. Pass', 'None or Primary', 1),
('6th Std. Pass', 'Class 10', 2),
('7th Std. Pass', 'Class 10', 2),
('8th Std. Pass', 'Class 10', 2),
('9th Std. Pass', 'Class 10', 2),
('10th Std. Pass', 'Class 10', 2),
('11th Std. Pass', 'Class 12 / Diploma', 3),
('12th Std. Pass', 'Class 12 / Diploma', 3)
) as t (education, edu, edu_level)
)
select
HH_ID,
REF_PERIOD_WAVE,
count(*) as Tot_N,
sum((AGE_YRS >= 60)::integer) as Old_N,
case
when max(edu_level) = 1 then 'None or Primary'
when max(edu_level) = 2 then 'Class 10'
when max(edu_level) = 3 then 'Class 12 / Diploma'
end max_hh_edu
from
input_table it
left join temp_equivalence te on it.EDUCATION = te.education
group by
HH_ID, REF_PERIOD_WAVE;
Obviously, you would need to complete all education level equivalences explicitly because you can't use the .default wild card with this approach and maybe the SQL dialect is a little different for MySQL, since I'm using Postgresql dialect here (sorry I haven't worked with MySQL in years), but I hope you get the general idea.