Solved

Adding a blank row when using union all

Posted on 2016-09-26
4
62 Views
Last Modified: 2016-09-26
When executing the following query, is there a way that I can insert a blank row in between both queries where the union all is?  Thanks!

select SCHOOLID as "School Name", 
COUNT(CASE WHEN grade_level = '1' THEN 1 end) "EGRADE1",
COUNT(CASE WHEN grade_level = '2' THEN 1 end) "EGRADE2",
COUNT(CASE WHEN grade_level = '3' THEN 1 end) "EGRADE2",
COUNT(*) "Total Student Enrollment",
from students
where enroll_status=0 AND SCHOOLID IN (SCHOOL1, SCHOOL2, SCHOOL3, SCHOOL4)
group by Rollup(SCHOOLID)
UNION ALL
select SCHOOLID as "School Name", 
COUNT(CASE WHEN grade_level = '1' AND S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) "SGRADE1",
COUNT(CASE WHEN grade_level = '2' AND S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) "SGRADE2",
COUNT(CASE WHEN grade_level = '3' AND S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) "SGRADE3",
COUNT(CASE WHEN S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) "Total SPED Enrollment"
from students
LEFT JOIN PS.S_CT_STU_LANGUAGE_X S_CT_STU_LANGUAGE_X ON STUDENTS.DCID = S_CT_STU_LANGUAGE_X.STUDENTSDCID
where enroll_status=0 AND SCHOOLID IN (SCHOOL1, SCHOOL2, SCHOOL3, SCHOOL4)
group by Rollup(SCHOOLID)

Open in new window

0
Comment
Question by:Basssque
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 41816627
add a query of all nulls

select <firstquery>
union all
select null,null,null,null,null from dual
union all
select <secondquery>
0
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 100 total points
ID: 41816629
Try like this:

select SCHOOLID as "School Name",
COUNT(CASE WHEN grade_level = '1' THEN 1 end) "EGRADE1",
COUNT(CASE WHEN grade_level = '2' THEN 1 end) "EGRADE2",
COUNT(CASE WHEN grade_level = '3' THEN 1 end) "EGRADE2",
COUNT(*) "Total Student Enrollment",
from students
where enroll_status=0 AND SCHOOLID IN (SCHOOL1, SCHOOL2, SCHOOL3, SCHOOL4)
group by Rollup(SCHOOLID)
UNION ALL

select '' as "School Name",
'' as "EGRADE1",
'' as "EGRADE2",
'' as "EGRADE2",
 '' as "Total Student Enrollment",
from dual
UNION ALL
select SCHOOLID as "School Name",
COUNT(CASE WHEN grade_level = '1' AND S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) "SGRADE1",
COUNT(CASE WHEN grade_level = '2' AND S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) "SGRADE2",
COUNT(CASE WHEN grade_level = '3' AND S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) "SGRADE3",
COUNT(CASE WHEN S_CT_STU_LANGUAGE_X.ELLINDICATOR = 'Y' THEN 1 end) "Total SPED Enrollment"
from students
LEFT JOIN PS.S_CT_STU_LANGUAGE_X S_CT_STU_LANGUAGE_X ON STUDENTS.DCID = S_CT_STU_LANGUAGE_X.STUDENTSDCID
where enroll_status=0 AND SCHOOLID IN (SCHOOL1, SCHOOL2, SCHOOL3, SCHOOL4)
group by Rollup(SCHOOLID)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41816630
You "can" but are you sure you want to?  I think you are trying to cram too much into a single query.

Anyway, here is a quick example of adding your blank line.

Note: I added a sort order column to the queries to ensure they come out in the correct order.

Remember, you need the same number of columns in ALL queries in a union so just add enough nulls to your blank line.
select col1 from (
	select 1 sortOrder, 'Hello' col1 from dual
	union all
	select 2 sortOrder, null from dual
	union all
	select 3 sortOrder, 'World' from dual
	order by sortOrder
)
/

Open in new window

0
 

Author Closing Comment

by:Basssque
ID: 41816644
Thanks to all!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now