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

Posted by Thiru under Sql Server on 6/22/2011 | Points: 10 | Views : 2424 | Status : [Member] | Replies : 7
Hi,
How can i get number of records available city wise using sql query.

ex: DB structure and data:

		
ID Name City
1 A CHN
2 B MUM
3 C DEL
4 D CHN
5 E CHN
6 F MUM
7 G DEL
8 H DEL
9 I DEL
10 J MUM

RESULT SHOULD BE
CITY Records
CHN 3
MUM 3
DEL 4


Can any one help me in this regard.
regards,
Thiru




Responses

Posted by: Thiru on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down

Resolved
Hi Vijay,
Thanks for your support - its working fine.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vforvijay on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
hi..try this Code

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


vbdotnetaddict.blogspot.com

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Vijay,
I like to go one more step in this query.
as in your above query i am getting the result as i expected.
now i like to separate that city and number or records month wise.

Like:

	

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


Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 6/23/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Can you pls try this...?
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

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vforvijay on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
hi...Try this code.........

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



vbdotnetaddict.blogspot.com

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vforvijay on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
hi thiru....

Try this code its working i check ti...

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


Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vforvijay on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
hi thiru....

Try this code its working i check ti...
select convert(varchar(3),DATENAME(month,dateofjoin)) as month from Yourtablename


Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response