By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,628 Members | 1,880 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,628 IT Pros & Developers. It's quick & easy.

Subquery headache with Count and GroupBy

P: n/a
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...

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
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!

Nov 23 '05 #3

P: n/a
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

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.