Group by Rollup

From: <patriciahide_at_gmail.com>
Date: 22 Feb 2006 07:58:05 -0800
Message-ID: <1140623885.310116.107980_at_z14g2000cwz.googlegroups.com>

CREATE TABLE orion.emp_ben_plan
(
area varchar2(30),
plan_type varchar2(20),
plan_name varchar2(60),
employee varchar2(60),
hvac_yn varchar2(1),
union_code varchar2(20));

insert into orion.emp_ben_plan values('Area 01','Dental','Aetna Dental Plan','John Doe','Y','Union 01');

insert into orion.emp_ben_plan values('Area 01','Dental','Aetna Dental Plan','Dave Thomas','Y','Union 01');

insert into orion.emp_ben_plan values('Area 01','Medical','Aetna Medical Plan','Dave Thomas','Y','Union 01');

insert into orion.emp_ben_plan values('Area 01','Medical','Aetna Medical Plan','Peter Abraham','Y','Union 01');

insert into orion.emp_ben_plan values('Area 01','Medical','Aetna Medical Plan','Tim Allen','N','Union 01');

insert into orion.emp_ben_plan values('Area 02','Medical','Aetna Medical Plan','Larry Adams','Y','Union 01');

commit;

select area,plan_name,hvac_yn,plan_type,employee,union_code from orion.emp_ben_plan
order by area,plan_name,hvac_yn

AREA	PLAN_NAME	HVAC_YN	PLAN_TYPE	EMPLOYEE	UNION_CODE
Area 01	Aetna Dental Plan	Y	Dental	John Doe	Union 01
Area 01	Aetna Dental Plan	Y	Dental	Dave Thomas	Union 01
Area 01	Aetna Medical Plan	N	Medical	Tim Allen	Union 01
Area 01	Aetna Medical Plan	Y	Medical	Dave Thomas	Union 01
Area 01	Aetna Medical Plan	Y	Medical	Peter Abraham	Union 01
Area 02	Aetna Medical Plan	N	Medical	Larry Adams	Union 01
Area 02	Aetna Medical Plan	Y	Medical	Larry Adams	Union 01

I want only totalcount whenever Area,Plan_name,hvac_yn changes and only on that level.

for ex.

AREA	PLAN_NAME	HVAC_YN	PLAN_TYPE	EMPLOYEE	UNION_CODE
Area 01	Aetna Dental Plan	Y	Dental	John Doe	Union 01
Area 01	Aetna Dental Plan	Y	Dental	Dave Thomas	Union 01


--  Total Record count Here


Area 01	Aetna Medical Plan	N	Medical	Tim Allen	Union 01


-- Total Record count Here


Area 01	Aetna Medical Plan	Y	Medical	Dave Thomas	Union 01
Area 01	Aetna Medical Plan	Y	Medical	Peter Abraham	Union 01


-- Total Record count Here


Area 02	Aetna Medical Plan	N	Medical	Larry Adams	Union 01


-- Total Record count Here


Area 02	Aetna Medical Plan	Y	Medical	Larry Adams	Union 01

-- Total Record count Here

I tried the following query but having hard time filtering out unwanted subtotals records..

select
area,plan_name,hvac_yn,plan_type,employee,union_code,grouping(area),grouping(plan_name),grouping(hvac_yn)  from orion.emp_ben_plan
group by rollup(area,plan_name,hvac_yn) , plan_type,employee,union_code order by area,plan_name,hvac_yn Received on Wed Feb 22 2006 - 16:58:05 CET