?
Solved

using order by with union all - oracly query

Posted on 2016-10-06
2
Medium Priority
?
60 Views
Last Modified: 2016-10-25
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'

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points (awarded by participants)
ID: 41832266
You can only have one order by on a unioned query.

Try simplified tests to see this.

This will reproduce the same error you described:
select 'hello' col from dual order by 1
union all
select 'world' from dual
/

Open in new window


Think about it this way:  The union says to treat all select statements as one query.  The order by is for the entire result set.

Add only one order by ad the end of all the unions:
select 'hello' col from dual
union all
select 'world' from dual
order by 1
/

Open in new window


That orders the results of both queries not just the second one.
2

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

752 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