using COUNT with 2 different field values

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)

using COUNT with 2 different field values

Author

Topic

mattashfield
Starting Member

2 Posts

Posted - 2010-12-15 : 11:47:34

Hi, Hope someone can help here.

I have some data (sample)

id, location
001,UK
002,US
003,Canada
004,US
005,EnglandUK

I want to do a COUNT on the location field but combine US and Canada into a single result and UK and EnglandUK into a single result. i expect my result set to look something like:

Location,MyCount
UK, 2
US,3

can someone help me here?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-15 : 12:31:47

[code]SELECT CASE
WHEN location in ('US', 'CANADA') THEN 'US'
WHEN location in ('UK', 'EnglandUK') THEN 'UK'
END as location,
Count(id) as myCount
FROM myTable
GROUP BY
CASE
WHEN location in ('US', 'CANADA') THEN 'US'
WHEN location in ('UK', 'EnglandUK') THEN 'UK'
END[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources