Re: Group by Rollup

[Quoted] Hello Patricia,

instead of a group by rollup you can use grouping sets:

SELECT AREA,

         PLAN_NAME,
         HVAC_YN,
         PLAN_TYPE,
         EMPLOYEE,
         UNION_CODE,
		 COUNT(*)
FROM     EMP_BEN_PLAN
GROUP BY GROUPING SETS(
	  	 				(AREA,
						 PLAN_NAME,
						 HVAC_YN
						 ),
						 (AREA,
						 PLAN_NAME,
						 HVAC_YN,
						 PLAN_TYPE,
						 EMPLOYEE,
						 UNION_CODE
						 )
						)
ORDER BY AREA,
         PLAN_NAME,
         HVAC_YN

Result:

AREA	PLAN_NAME	HVAC_YN	PLAN_TYPE	EMPLOYEE	UNION_CODE	COUNT(*)
Area 01	Aetna Dental PLAN	Y	Dental	JOHN DOE	UNION 01	1
Area 01	Aetna Dental PLAN	Y	Dental	DAVE THOMAS	UNION 01	1
Area 01	Aetna Dental PLAN	Y				2
Area 01	Aetna MEDICAL PLAN	N	Medical	TIM ALLEN	UNION 01	1
Area 01	Aetna MEDICAL PLAN	N				1
Area 01	Aetna MEDICAL PLAN	Y	Medical	DAVE THOMAS	UNION 01	1
Area 01	Aetna MEDICAL PLAN	Y	Medical	PETER ABRAHAM	UNION 01	1
Area 01	Aetna MEDICAL PLAN	Y				2
Area 02	Aetna MEDICAL PLAN	N	Medical	LARRY ADAMS	UNION 01	1
Area 02	Aetna MEDICAL PLAN	N				1
Area 02	Aetna MEDICAL PLAN	Y	Medical	LARRY ADAMS	UNION 01	1
Area 02	Aetna MEDICAL PLAN	Y				1

Regards,

Jörg Received on Wed Feb 22 2006 - 18:02:53 CET