The GROUP BY Clause - 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

SQL Server 2000 Forums

Transact-SQL (2000)

The GROUP BY Clause

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2005-07-19 : 15:21:52

It always bothers me when I write a query like this:

select case when (w.Patent is NULL) then 0 else 1 end as JoinType,
Count (*) as [# Records]

from t_Deferred d
left join t_World w on d.id = w.id

group by case when (w.Patent is NULL) then 0 else 1 end

This bothers me because the Group By clause repeats the function used in the Select Clause. It seems like I should be able to write ...

GROUP BY JoinType

... but I'm not able to. Is this the best way to code it?

Dom

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-19 : 15:32:04

Here is an alternative, but I really don't know if its an improvement. You'd have to do some analysis and compare execution plans.

select	joinType
	,Count (*) as [# Records]
from	(	
	select	case 
		when (w.Patent is NULL) then 0 
		else 1 
		end as JoinType
	from	t_Deferred d
	left join t_World w on d.id = w.id
	) a
group by joinType

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources