471,599 Members | 1,009 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,599 software developers and data experts.

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

Nov 23 '05 #1
3 2017
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
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
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.

Similar topics

4 posts views Thread by Kenny G | last post: by
2 posts views Thread by Edwin Pauli | last post: by
4 posts views Thread by Bryan | last post: by
20 posts views Thread by Frank Millman | last post: by
10 posts views Thread by 7stud | last post: by
2 posts views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.