Grouping - 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)

Grouping

Author

Topic

shwelch
Starting Member

33 Posts

Posted - 2006-10-10 : 07:25:37

I have a table with thousand of records and a another table that I am using as a lookup table to join records:

Table1
ID
field1
field2
field3

Lookup Table
Abbrev
Fullname

I am using something like:
Select Field1, field2, fullname
from table1 join
[lookup table] on
table1.field3 = [lookup table].abbrev
group by fullname, field2, field1

This works fine. Now, in the same query, I want to get all of the other records that do not join, and classify them into a group on their on, "other". Is there a quick way of doing this? I would want the other to be available for grouping in the place where fullname is there for all of the other joined records.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 07:39:25

This should give you a starter

Select		ISNULL(t1.fullname, 'Others') AS FullName,
		t1.Field1,
		SUM(t2.Field2)
from		table1 t1
LEFT join	[lookup table] t2 on t2.abbrev = t1.field3
group by	t1.fullname,
		t1.field2

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-10 : 08:51:31

can also have a look at the full outer join

Chirag

http://chirikworld.blogspot.com/

shwelch
Starting Member

33 Posts

Posted - 2006-10-10 : 09:04:09

Thanks to both of ya, it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources