group by case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

group by case statement

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-05-13 : 21:08:11

Hi guys,
I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below.
I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements).
Any feedback would be appreciated


SELECT distinct 
		PERIOD.STARTDATE, 
		temp_111.EVENTTYPEID,
		case when temp_111.[name] like '%GST%' 
			then sum(temp_111.CONTRIBUTIONVALUE) 
		end as GST_AMOUNT,
		case when temp_111.[name] not like '%GST%'  
			then sum(temp_111.CONTRIBUTIONVALUE) 
		end as Total_Amount
FROM         	temp_111 INNER JOIN PERIOD 
			ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ 
WHERE     	
		(NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL)
		AND temp_111.PERIODSEQ in ('111')
group by 	PERIOD.STARTDATE, 
		temp_111.EVENTTYPEID,
		temp_111.[name]

Here is the current result displayed:


Startdate               eventtypeid gst_amount        Total_amount
2006-11-01 00:00:00.000	Nel	NULL	              83470.5608000000
2006-11-01 00:00:00.000	Nel	NULL	             161408.5264874810
2006-11-01 00:00:00.000	Nel	NULL	             677568.2683000000
2006-11-01 00:00:00.000	Nel	NULL	            2645478.1215092400
2006-11-01 00:00:00.000	Nel	8347.0560800000	        NULL
2006-11-01 00:00:00.000	Nel	16140.8526488160	NULL
2006-11-01 00:00:00.000	Nel	67756.8268300000	NULL
2006-11-01 00:00:00.000	Nel	264547.8121507070	NULL

Instead I want the result to show something like this:


Startdate               eventtypeid gst_amount        Total_amount
2006-11-01 00:00:00.000	Nel	8347.0560800000       83470.5608000000
2006-11-01 00:00:00.000	Nel	16140.8526488160     161408.5264874810
2006-11-01 00:00:00.000	Nel	67756.8268300000     677568.2683000000
2006-11-01 00:00:00.000	Nel	264547.8121507070   2645478.1215092400

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-13 : 21:45:38

try this


SELECT distinct 
		PERIOD.STARTDATE, 
		temp_111.EVENTTYPEID,
		sum(case when temp_111.[name] like '%GST%' 
			then sum(temp_111.CONTRIBUTIONVALUE) 
		end) as GST_AMOUNT,
		sum(case when temp_111.[name] not like '%GST%'  
			then sum(temp_111.CONTRIBUTIONVALUE) 
		end) as Total_Amount
FROM         	temp_111 INNER JOIN PERIOD 
			ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ 
WHERE     	
		(NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL)
		AND temp_111.PERIODSEQ in ('111')
group by 	PERIOD.STARTDATE, 
		temp_111.EVENTTYPEID,
		temp_111.[name]

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-05-13 : 23:52:02

Perfect!
Many Thanks.
Champinco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources