Query to find no. of records available city wise [Resolved]

select count(*) as records, City from (tablename)  group by City

	

ID      Name    City	date of join	

        1       A       CHN	01-Jan-11	

        2       B       MUM	01-Mar-11	

        3       C       DEL	01-Mar-11	

        4       D       CHN	01-Jan-11	

        5       E       CHN	01-Mar-11	

        6       F       MUM	01-Jan-11	

        7       G       DEL	01-Jan-12	

        8       H       DEL	01-Mar-12	

        9       I       DEL	01-Jan-11	

        10      J       MUM	01-Jan-11	

		

                        		

		

        RESULT SHOULD BE                		

month	city	records

JAN	CHN	2

JAN	DEL	2

JAN	MUM	2

MAR	CHN	1

MAR	DEL	2

MAR	MUM	1

Select [Month], City,COUNT(1) [Records] from

(

select CAST(DATENAME(MM,[Date of Join]) AS VARCHAR(3)) [Month],City,[Date of Join] from <YourTableName>

) X

Group By [Month], City

Order by Cast('2011-' + [Month] + '-01' as datetime)

Month	City	Records

Jan	CHN	2

Jan	DEL	2

Jan	MUM	2

Mar	MUM	1

Mar	DEL	2 

Mar	CHN	1

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

select count(*) as records, City,date of join from (tablename)  group by City,date of join

select count(*) as records, City,CAST(DATENAME(MM,[dateofjoin]) AS VARCHAR(3)) as month group by City,date of join

select convert(varchar(3),DATENAME(month,dateofjoin)) as month from Yourtablename