Solved

Adding a blank row when using union all

Posted on 2016-09-26
4
49 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

18 Experts available now in Live!

Get 1:1 Help Now