About Pricing Community Teams Start Free Trial

Basssque

asked on 9/26/2016

Adding a blank row when using union all

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

Oracle DatabaseSQL

Last Comment

Basssque

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

9/26/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

SOLUTION

9/26/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

slightwv (䄆 Netminder)

9/26/2016

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

Basssque

9/26/2016

ASKER

Thanks to all!

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

Plans and Pricing

Certified Expert Program

Credly Partnership

Udemy Partnership