• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

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

0
Basssque
Asked:
Basssque
2 Solutions
 
sdstuberCommented:
add a query of all nulls

select <firstquery>
union all
select null,null,null,null,null from dual
union all
select <secondquery>
0
 
Haris DjulicCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
BasssqueAuthor Commented:
Thanks to all!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now