SQL & PL/SQL

Hi

I need sum the month totals

SELECT    region_name, area_name
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '07' THEN sales_target END)	 AS jul  
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '08' THEN sales_target END)	AS aug
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '09' THEN sales_target END)	AS sep
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '10' THEN sales_target END)	AS oct
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '11' THEN sales_target END)	AS nov
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '12' THEN sales_target END)	AS dec
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '01' THEN sales_target END)	AS jan
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '02' THEN sales_target END)	AS feb
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '03' THEN sales_target END)	AS mar
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '04' THEN sales_target END)	AS apr
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '05' THEN sales_target END)	AS may
	,SUM (CASE WHEN SUBSTR(yr_mth,5,2) = '06' THEN sales_target END)	AS jun
FROM	sales_forecast WHERE 	REGION_NAME like nvl('&REGIONNAME','%') AND
			 	AREA_NAME like nvl('&AREANAME','%') AND
			yr_mth=nvl('&Year',substr(yr_mth,1,4))||NVL(DECODE('&Month','JAN','01','FEB','02','MAR','03','APR','04','MAY','05',
      'JUN','06','JUL','07','AUG','08','SEP','09','NOV','11','DEC','12'),substr(yr_mth,5,6))
GROUP BY  region_name, area_name
ORDER BY  region_name, area_name

Requirment

region_name,  area_name,  jul,   aug  ................ jun
abc                xyz             10,   20.....................30
ccc                ggg             20,  30.....................40
total                                30,   50 ...................70

thanks

NR

Edited by: user10966033 on 16 juin 2009 06:46