MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table support_type (
  support_id char(3),
  support_type varchar(50),
  support_days numeric(9,2),
  billing_cost int
);

insert into support_type values
('smt','staff meeting',0.25,125),
('acs','additional cluster session',0.5,250),
('hds','half day support',0.5,250),
('rau','reading audit', 0.75,375),
('wau','writing audit', 0.75,375),
('fds','full day support', 1,500);

create table support_record (
  school_id char(3),
  smt int
);

insert into support_record values
('S01', 1),
('S02', 1),
('S03', 2),
('S04', 3),
('S05', 0);

select sr.school_id,
  sum(sr.smt * case st.support_id when 'rau' then st.support_days else 0 end) as rau_support_total,
  sum(sr.smt * case st.support_id when 'wau' then st.support_days else 0 end) as wau_support_total,
  sum(sr.smt * case st.support_id when 'smt' then st.support_days else 0 end) as smt_support_total,
  sum(sr.smt * case st.support_id when 'hds' then st.support_days else 0 end) as hds_support_total,
  sum(sr.smt * case st.support_id when 'fds' then st.support_days else 0 end) as fds_support_total,
  sum(sr.smt * st.support_days) as support_days_total
from support_record as sr
cross join support_type as st
group by sr.school_id
order by sr.school_id;

select sr.school_id,
  sum(sr.smt * (select support_days from support_type where support_id = 'rau')) as rau_support_total,
  sum(sr.smt * (select support_days from support_type where support_id = 'wau')) as wau_support_total,
  sum(sr.smt * (select support_days from support_type where support_id = 'smt')) as smt_support_total,
  sum(sr.smt * (select support_days from support_type where support_id = 'hds')) as hds_support_total,
  sum(sr.smt * (select support_days from support_type where support_id = 'fds')) as fds_support_total,
  sum(sr.smt * (select sum(support_days) from support_type)) as support_days_total
from support_record as sr
group by sr.school_id
order by sr.school_id;

Records: 6  Duplicates: 0  Warnings: 0

Records: 5  Duplicates: 0  Warnings: 0

school_id	rau_support_total	wau_support_total	smt_support_total	hds_support_total	fds_support_total	support_days_total
S01	0.75	0.75	0.25	0.50	1.00	3.75
S02	0.75	0.75	0.25	0.50	1.00	3.75
S03	1.50	1.50	0.50	1.00	2.00	7.50
S04	2.25	2.25	0.75	1.50	3.00	11.25
S05	0.00	0.00	0.00	0.00	0.00	0.00

school_id	rau_support_total	wau_support_total	smt_support_total	hds_support_total	fds_support_total	support_days_total
S01	0.75	0.75	0.25	0.50	1.00	3.75
S02	0.75	0.75	0.25	0.50	1.00	3.75
S03	1.50	1.50	0.50	1.00	2.00	7.50
S04	2.25	2.25	0.75	1.50	3.00	11.25
S05	0.00	0.00	0.00	0.00	0.00	0.00