Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Venkat.

Asked: January 09, 2019 - 3:14 pm UTC

Last updated: January 10, 2019 - 7:27 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

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

and Connor said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you.

A reader, January 10, 2019 - 4:15 pm UTC

I will go this route, and test out the script.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library