group by - 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 2008 Forums

Transact-SQL (2008)

group by

Author

Topic

mwwheeler
Starting Member

2 Posts

Posted - 2009-11-26 : 09:28:46

I need to return 2 columns in a group by using a having clause
I only want results if the data in one column has duplicates
(they arent strictly speaking duplicates, since there are several towns with the same name)

something like this

select TownName, CountyName FROM Town
INNER JOIN County
ON County.CountyID = Town.CountyID
GROUP BY TownName, CountyName
having count(TownName) > 1

this returns no data, but there are duplicate town names in the town table, so id want to return those along with the associated county

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 10:00:13

select t2.TownName, t1.CountyName FROM County as t1
inner join
(
select TownName FROM Town
group by TownName
having count(TownName) > 1
) as t2
on t1.CountyID = t2.CountyID

Madhivanan

Failing to plan is Planning to fail

mwwheeler
Starting Member

2 Posts

Posted - 2009-11-26 : 10:43:19

thats doesnt seem to work, t2 doesnt have a countyID as only the townname is selected

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 01:42:56

select t2.TownName, t1.CountyName FROM County as t1
inner join
(
select Countryid FROM Town
group by Countryid
having count(TownName) > 1
) as t2
on t1.CountyID = t2.CountyID

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources