473,322 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 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 2095
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Nachi | last post by:
I am getting 2 resultsets depending on conditon, In the second conditon i am getting the above error could anyone help me.......... CREATE proc sp_count_AllNewsPapers @CustomerId int as...
2
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n...
0
by: Greg Stark | last post by:
Postgresql 7.4b2 (approximately, compiled out of CVS) When I have a subquery that has a complex subquery as one of the result columns, and then that result column is used multiple times in the...
4
by: Kenny G | last post by:
Below is a query that I currently have. I need to produce a subquery so that the top five of the CodeCount is returned. I appreciate your help. SELECT .PX_SURGEON, .PX_CODE, Count(.PX_CODE)...
2
by: Marcus | last post by:
I have noticed something odd on one of my queries. Maybe someone can help or explain. I am using the GroupBy function on all my rows except for pulling "Min" StartDate, "Max" EndDate, and "Min"...
2
by: Edwin Pauli | last post by:
Hi, Yesterday i had upgrade my PostgreSQL server from version 7.2.4 to 7.4.1. There are troubles with a subquery after the upgrade. Here is the query: SELECT team_naam, team_id, wpim, (
4
by: Bryan | last post by:
can some explain why in the 2nd example, m doesn't print the list which i had expected? >>> for k, g in groupby(): .... print k, list(g) .... 1 2 3
20
by: Frank Millman | last post by:
Hi all This is probably old hat to most of you, but for me it was a revelation, so I thought I would share it in case someone has a similar requirement. I had to convert an old program that...
10
by: 7stud | last post by:
I'm applying groupby() in a very simplistic way to split up some data, but when I timeit against another method, it takes twice as long. The following groupby() code groups the data between the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.