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 Total
M1 1
Z1 2
Kindly help me to make query using SQLServer 05

russellbNew Member

27 Mar 2012 08:09 AM

I don't understand. I see two M1 and three Z1...

Mr ProgrammerNew Member

27 Mar 2012 10:52 AM

101 percent its working
thanks
select t.townname,count(distinct(t.towncode))as total from town t
group by t.townname

S.KhanzhinNew Member

16 Apr 2012 10:54 AM

The same result with this query

with townRank as ( select townname, dense_rank() over (partition by townname order by towncode) rank from town ) select townname, max(rank) rank from townRank group by townname