Group by with Case

[manmaria]

I have a sql like this and it works

select col1,col2,'Y' from tab1, tab2
where tab1.col1=tab2.col3
group by col1,col2

But I want to add CASE to determine 'Y', like this

select col1,col2,
case when tab2.col5='05' THEN 'Y'
ELSE 'N' from tab1, tab2
where tab1.col1=tab2.col3
group by col1,col2

Looks like I need to add COL5 in the group by. Is there any way I can avoid adding the col5 in to the group by.

Thanks in advance,

 

[BrianTyler]

You will need to repeat the whole case statement in the group by - easily done with copy & paste. In effect it is usual to have all of the non-aggregate fields repeated in the ,group by'

 

[MarcLodge]

Sorry Manmaria and Brian, but am I missing something here? I can't see any reason whatsoever for having a GROUP BY as there is no aggregating going on that warrants it. You appear to be doing just a straight forward SELECT, so why the GROUP BY?

Marc

 

[BrianTyler]

Marc,

You are right of course. I was just being too lazy reading the question.

Brian
===========================

ManMaria

If you are unclear about the need for 'group by' in queries, please feel free to ask

Brian

 

[ummadhira]

I am doing the group by to avoid the duplicates.

Regards,

 

[MarcLodge]

Hi ummadhira, if you wish to avoid duplicates, then use DISTINCT. GROUP BY is traditionally used when you are performing an arithmetic function like COUNT or SUM or AVG.

Get back to us if this doesn't make sense and you require a little more help.

Marc