Subquery headache with Count and GroupBy

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...

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

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!

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