create view dbo.vw_student_prct as

select TOP(100) percent a.totalstudents,
b.totalscience students,
cast(cast((case when b.totalsciencesstudents is null then 0 else b.totalsciencestudents end)as decimal(8,2))
 / (case when a.totalstudents is null then 1 else a.totalstudents end )* 100 as decimal(8,2))  as scienceprnct,

--
--
--
b.st_date

from
(
select st_date,count(student_id) as totalstudents
from student as studnt1
where (ds_name like '%Doere%')
group by st_date) as a inner join
(
select st_date,count(student_id) as totalsciencestudents
from student as studnt
where (ss_name like '%physical%')
group by st_date) as b on a.student_date = b.student_date inner join
(
select st_date,count(student_id) as totalMathstudents
from student as studnt2
where (ss_name like '%math%')
group by st_date) as c on c.student_date = b.student_date inner join
(
select st_date,count(student_id) as totalenglishstudents
from student as studnt1
where (ts_name like '%eng%')
group by st_date) as d on b.student_date = d.student_date inner join

(
select st_date,count(student_id) as totalspecialstudystudents
from student as studnt1
where (ts_name like '%speci;%') OR (ts_name like '%speci;%') OR (ts_name like '%speci')  OR (ts_name like '%speciA%')
OR (ts_name like '%speciD%') OR (ts_name like '%speciTo%') OR (ts_name like '%speciK%')
group by st_date) as e on b.student_date = b.student_date inner join

(
select st_date,count(student_id) as totalkainistudents
from student as studnt3
where (Ks_name like '%kaini%') and (Lstatus ='Y'AND (kstatus ='Y')
OR (ss_name like '%speci;%')  and (lstatus = 'Y') AND (kstatus ='Y') 
OR (ks_name like '%seci')  OR (ts_name like '%VteciA%')
group by st_date) as f on b.student_date = f.student_date 
)
order by b.st_date