Hello-
I have a script as follows:
SELECT
s.schoolid sid,
s.lastfirst lf,
s.grade_level grl,
sum(ada.membershipvalue)-sum(ada.attendancevalue) absences,
sum(ada.membershipvalue) membership,
round((sum(ada.membershipvalue)-sum(ada.attendancevalue))/sum(ada.membershipvalue)*100,2) absenceRate,
round(sum(ada.attendancevalue)/sum(ada.membershipvalue)*100,2) PresentRate,
case when round(sum(ada.attendancevalue)/sum(ada.membershipvalue)*100,2) <= 89.99 then 'Red' else 'Green' end attctg
FROM students s
JOIN schools schl ON schl.school_number = s.schoolid
JOIN ps_adaadm_defaults_all ada ON ada.studentid = s.id
WHERE
s.schoolid = 1
and s.enroll_status = 0
AND to_date(ada.calendardate) >= to_date('09/01/2018','MM/DD/YYYY') -- param start dt of sch
AND to_date(ada.calendardate) <= to_date(current_date)
AND ada.membershipvalue > 0
GROUP BY s.schoolid,
s.lastfirst,
s.grade_level
How can I modify this script to show me percent of students who fall in the respective category (Red and Green) by Grade level?
Thanks
Venkat
Well...without any real context given I'd strongly you advise you test the validity of the answer, but something like this should do it:
with grouped_data as
(
SELECT
s.schoolid sid,
s.lastfirst lf,
s.grade_level grl,
sum(ada.membershipvalue)-sum(ada.attendancevalue) absences,
sum(ada.membershipvalue) membership,
round((sum(ada.membershipvalue)-sum(ada.attendancevalue))/sum(ada.membershipvalue)*100,2) absenceRate,
round(sum(ada.attendancevalue)/sum(ada.membershipvalue)*100,2) PresentRate,
case when round(sum(ada.attendancevalue)/sum(ada.membershipvalue)*100,2) <= 89.99 then 'Red' else 'Green' end attctg,
count(*) cnt_per_color
FROM students s
JOIN schools schl ON schl.school_number = s.schoolid
JOIN ps_adaadm_defaults_all ada ON ada.studentid = s.id
WHERE
s.schoolid = 1
and s.enroll_status = 0
AND to_date(ada.calendardate) >= to_date('09/01/2018','MM/DD/YYYY') -- param start dt of sch
AND to_date(ada.calendardate) <= to_date(current_date)
AND ada.membershipvalue > 0
GROUP BY s.schoolid,
s.lastfirst,
s.grade_level
)
select
sid,
lf,
grl,
absences,
membership,
absenceRate,
PresentRate,
attctg,
100 * cnt_per_color / sum(cnt_per_color) over ( partition by sid,lf,grl,absenceRate,PresentRate) as color_pct
from grouped_data
I added "count_per_color" then used an analytic function to the each count divided by the sum of the counts across both red and green.