Keeping Area Code COUNT - 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 2000 Forums

Transact-SQL (2000)

Keeping Area Code COUNT

Author

Topic

richardjamison
Starting Member

3 Posts

Posted - 2010-12-06 : 10:40:04

Example: I have a table with the following columns.
AreaCode, Phone, UserName

I want to create a SELECT statement that returns:
AreaCode, Phone, UserName, AreaCodeCount

so if i have the following data in my table:
555, 555-2424, joe
554, 555-2323, jane
554, 555-2222, josh
554, 555-2121, jamie
553, 555-2020, jake
553, 555-1919, julie
552, 555-1818, jessie

i want to return:
555, 555-2424, joe, 1
554, 555-2323, jane, 3
554, 555-2222, josh, 3
554, 555-2121, jamie, 3
553, 555-2020, jake, 2
553, 555-1919, julie, 2
552, 555-1818, jessie, 1

I'm a newbie in school so I appologize if this is a simple fix and I'm just overlooking the solution.

Thanks,

Rich

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-06 : 10:43:20

SELECT s.AreaCode, s.Phone, s.UserName, (select count(*) from dbo.table1 AS x where x.areacode = s.areacode) AS AreaCodeCount
FROM dbo.Table1 AS s

N 56°04'39.26"
E 12°55'05.63"

richardjamison
Starting Member

3 Posts

Posted - 2010-12-06 : 10:51:30

worked like a champ, thank you very much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-06 : 11:03:26

You should upgrade to SQL Server 2008 or later. SQL Server 2000 is outdated and you have no support for the product from Microsoft no longer.

With SQL Server 2008, the query would look like

SELECT AreaCode, Phone, UserName, count(*) over (partition by areacode) AS AreaCodeCount
FROM dbo.Table1

N 56°04'39.26"
E 12°55'05.63"

richardjamison
Starting Member

3 Posts

Posted - 2010-12-06 : 12:47:19

I understand, unfortunately i dont make those decisions. I'd like it if it were so. I could do things a bit easier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources