Solved

Calculating total percentage per row in Oracle Query

Posted on 2016-10-04
2
72 Views
Last Modified: 2016-10-04
I need to add a column that gives me a total percentage.  The issue I'm having is, I need to get the percentage of each row against the total number of students regardless of if S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' or not.  For example, if I had 1000 students enrolled total and the total of row 1 equals 20, then the total percentage for row 1 would be 2%.  Thanks!!

SELECT
SCHOOLS.NAME as "School",
COUNT(CASE WHEN grade_level = '9' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "9",
COUNT(CASE WHEN grade_level = '10' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "10",
COUNT(CASE WHEN grade_level = '11' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "11",
COUNT(CASE WHEN grade_level = '12' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "12"
from PS.STUDENTS STUDENTS
LEFT JOIN PS.SCHOOLS SCHOOLS on STUDENTS.SCHOOLID = SCHOOLS.SCHOOL_NUMBER
LEFT JOIN PS.S_CT_STU_SPED_X S_CT_STU_SPED_X ON STUDENTS.DCID = S_CT_STU_SPED_X.STUDENTSDCID
where enroll_status=0 
AND SCHOOLID IN (3, 4, 7, 8, 9)
group by SCHOOLS.NAME

Open in new window

0
Comment
Question by:Basssque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41828015
SELECT
SCHOOLS.NAME as "School",
COUNT(CASE WHEN grade_level = '9' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "9",
COUNT(CASE WHEN grade_level = '10' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "10",
COUNT(CASE WHEN grade_level = '11' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "11",
COUNT(CASE WHEN grade_level = '12' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "12",
round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2) pct_in_special_education
from PS.STUDENTS STUDENTS
LEFT JOIN PS.SCHOOLS SCHOOLS on STUDENTS.SCHOOLID = SCHOOLS.SCHOOL_NUMBER
LEFT JOIN PS.S_CT_STU_SPED_X S_CT_STU_SPED_X ON STUDENTS.DCID = S_CT_STU_SPED_X.STUDENTSDCID
where enroll_status=0 
AND SCHOOLID IN (3, 4, 7, 8, 9)
group by SCHOOLS.NAME

Open in new window

1
 

Author Comment

by:Basssque
ID: 41828051
Perfect, thank you!!!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question