| Author |
Subquery headache with Count and GroupBy
|
|
| laurenq uantrell 2005-11-22, 11:23 am |
| 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 2005-11-22, 11:23 am |
| 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 2005-11-22, 11:23 am |
| 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 2005-11-22, 11:23 am |
| 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
|
|
|
|