troubleshooting Question

using order by with union all - oracly query

Avatar of Basssque
Basssque asked on
Oracle DatabaseSQL
1 Comment1 Solution81 ViewsLast Modified:
Using the query below, I'm trying to set the order of the grade_level column.  If I put order by grade_level in the query I want to set the order in, it says the query isn't ended properly.  If I put it at the very bottom, is says the field is invalid.  I'm stuck on this one, any help is appreciated.  thanks!


UNION ALL
SELECT 'Totals (schools above only)' as School,null,null,null,null,null,null,null,null,null,null,null,null,null,null FROM dual
UNION ALL
SELECT 
null,grade_level,null,null,null,null,null,null,null,null,
COUNT(CASE WHEN S_CT_STU_DEMOGRAPHICS_X.GIFTEDTALENTED = '09' THEN 1 end) ALP,
COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) SPED,
COUNT(CASE WHEN ETHNICITY <> '04' THEN 1 end) Minority,
COUNT(CASE WHEN S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) ESOL,
COUNT(student_number) cnt
FROM students
LEFT JOIN PS.S_CT_STU_SPED_X S_CT_STU_SPED_X ON STUDENTS.DCID = S_CT_STU_SPED_X.STUDENTSDCID
LEFT JOIN PS.S_CT_STU_LANGUAGE_X S_CT_STU_LANGUAGE_X ON STUDENTS.DCID = S_CT_STU_LANGUAGE_X.STUDENTSDCID
LEFT JOIN PS.S_CT_STU_DEMOGRAPHICS_X S_CT_STU_DEMOGRAPHICS_X ON STUDENTS.DCID = S_CT_STU_DEMOGRAPHICS_X.STUDENTSDCID
WHERE enroll_status = 0 AND schoolid IN (3, 4, 8, 9, 10, 13, 14, 15, 22) AND lower(last_name) != 'doe'
GROUP BY grade_level
UNION ALL
SELECT 
'totals' as totals,null,null,null,null,null,null,null,null,null,
COUNT(CASE WHEN S_CT_STU_DEMOGRAPHICS_X.GIFTEDTALENTED = '09' THEN 1 end) ALP,
COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) SPED,
COUNT(CASE WHEN ETHNICITY <> '04' THEN 1 end) Minority,
COUNT(CASE WHEN S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) ESOL,
COUNT(student_number) cnt
FROM students
LEFT JOIN PS.S_CT_STU_SPED_X S_CT_STU_SPED_X ON STUDENTS.DCID = S_CT_STU_SPED_X.STUDENTSDCID
LEFT JOIN PS.S_CT_STU_LANGUAGE_X S_CT_STU_LANGUAGE_X ON STUDENTS.DCID = S_CT_STU_LANGUAGE_X.STUDENTSDCID
LEFT JOIN PS.S_CT_STU_DEMOGRAPHICS_X S_CT_STU_DEMOGRAPHICS_X ON STUDENTS.DCID = S_CT_STU_DEMOGRAPHICS_X.STUDENTSDCID
WHERE enroll_status = 0 AND schoolid IN (3, 4, 8, 9, 10, 13, 14, 15, 22) AND lower(last_name) != 'doe'
UNION ALL
SELECT 
'total percentages' as percentage,null,null,null,null,null,null,null,null,null,
ROUND(COUNT(CASE WHEN S_CT_STU_DEMOGRAPHICS_X.GIFTEDTALENTED = '09' THEN 1 end)/ count(*) * 100) ALP,
ROUND(COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100) SPED,
ROUND(COUNT(CASE WHEN ETHNICITY <> '04' THEN 1 end)/ count(*) * 100) Minority,
ROUND(COUNT(CASE WHEN S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end)/ count(*) * 100) ESOL,
null
FROM students
LEFT JOIN PS.S_CT_STU_SPED_X S_CT_STU_SPED_X ON STUDENTS.DCID = S_CT_STU_SPED_X.STUDENTSDCID
LEFT JOIN PS.S_CT_STU_LANGUAGE_X S_CT_STU_LANGUAGE_X ON STUDENTS.DCID = S_CT_STU_LANGUAGE_X.STUDENTSDCID
LEFT JOIN PS.S_CT_STU_DEMOGRAPHICS_X S_CT_STU_DEMOGRAPHICS_X ON STUDENTS.DCID = S_CT_STU_DEMOGRAPHICS_X.STUDENTSDCID
WHERE enroll_status = 0 AND schoolid IN (3, 4, 8, 9, 10, 13, 14, 15, 22) AND lower(last_name) != 'doe'
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros