Subquery headache with Count and GroupBy

[laurenq uantrell]

I'm trying to return an integer from the following table that returns
the number of unique cities:
tblEmployees
Name City
John Boston
Frank New York
Jim Omaha
Betty New York

The answer should be 3.

DECLARE @EmployeeCities int

SELECT @EmployeeCities = SELECT ... ???

How go I return one row/column into @EmployeeCities from a Count and a
GroupBy?

Headache already... Maybe it's too early...

[Robert Klemme]

laurenq uantrell wrote:

I'm trying to return an integer from the following table that returns
the number of unique cities:
tblEmployees
Name City
John Boston
Frank New York
Jim Omaha
Betty New York

The answer should be 3.

DECLARE @EmployeeCities int

SELECT @EmployeeCities = SELECT ... ???

How go I return one row/column into @EmployeeCities from a Count and a
GroupBy?

Headache already... Maybe it's too early...

select @EmployeeCities = select count(distinct City) from tblEmployees

Kind regards

robert

[laurenq uantrell]

Jees! I'd better start drinking coffee in the morning! Thanks. I must
have wasted 30 minutes trying to put a GROUPBY subquery inside a COUNT
subquery before seeking help!

[Robert Klemme]

laurenq uantrell wrote:

Jees! I'd better start drinking coffee in the morning!
:-))
Thanks.
In fact there was a typo - 1 "select" too much:

select @EmployeeCities = count(distinct City) from tblEmployees
I must
have wasted 30 minutes trying to put a GROUPBY subquery inside a COUNT
subquery before seeking help!

You mean like

select @EmployeeCities = count(*) from (select City from tblEmployees
group by City) x

?

Cheers

robert