How to make query using sqlserver

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 2005 Forums

Transact-SQL (2005)

How to make query using sqlserver

Author

Topic

qasimidl
Starting Member

10 Posts

Posted - 2012-03-27 : 05:14:02

i have to make query

My table structure is like this

towncodeNo TownName

2023001 M1
2023001 M1
2031025 Z1
2031025 Z1
2031030 Z1

Result should be

Townname TotalTown
M1 1
Z1 2

Kindly help me to make query using SQLServer 05

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-27 : 05:23:56

Look up COUNT DISTINCT

--
Gail Shaw
SQL Server MVP

qasimidl
Starting Member

10 Posts

Posted - 2012-03-27 : 07:25:29

I have solved

solution

select t.townname,count(distinct(t.towncode)) from town t
group by t.townname,t.towncode

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-27 : 08:08:48

That won't work.

That will give you:
M1 1
Z1 1
Z1 1

You want to group by the town name, not both the name and code. You almost never want to group by a column you are aggregating, just doesn't make sense to do so.

--
Gail Shaw
SQL Server MVP

qasimidl
Starting Member

10 Posts

Posted - 2012-03-27 : 14:47:59

select t.townname,count(distinct(t.towncode))as total from town t
group by t.townname

101percent its working
thanks

quote:
Originally posted by GilaMonster

That won't work.

That will give you:
M1 1
Z1 1
Z1 1

You want to group by the town name, not both the name and code. You almost never want to group by a column you are aggregating, just doesn't make sense to do so.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources