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

Another SQL/query question

P: n/a
DH
Say I have a table with two columns that matter to this example,
[PointNumber] and [SurveyDate]. And here is an example of what the rows
currently look like:

[PointNumber], [SurveyDate]
1,6/27/2001
1,6/27/2001
1,5/31/2001
2,6/27/2001
2,6/30/2001
2,5/31/2001

I want a query that will return PointNumber and the number of surveys (ie.
how many different dates are in the table for each point). For the example
above, the query should output:

[PointNumber], [NumberOfSurveys]
1,2
2,3

I'd appreaciate any assistance. This seems like it would be simple but I
don't know how to do it.

Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
first make a query that returns unique pointnumber, surveydate combinations
(the 'unique values' query property will turn on 'select distinct')
then using that query, make a query that groups by pointnumber and counts
surveydate.
"DH" <dana_hartley@(remove)hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Say I have a table with two columns that matter to this example,
[PointNumber] and [SurveyDate]. And here is an example of what the rows
currently look like:

[PointNumber], [SurveyDate]
1,6/27/2001
1,6/27/2001
1,5/31/2001
2,6/27/2001
2,6/30/2001
2,5/31/2001

I want a query that will return PointNumber and the number of surveys (ie.
how many different dates are in the table for each point). For the example
above, the query should output:

[PointNumber], [NumberOfSurveys]
1,2
2,3

I'd appreaciate any assistance. This seems like it would be simple but I
don't know how to do it.

Nov 12 '05 #2

P: n/a
ONE query is all you need
Select PointNumber, Count(*) from
YourTable
group by PointNumber
"dogwalker" <d@g.com> wrote in message
news:30******************@news20.bellglobal.com...
first make a query that returns unique pointnumber, surveydate combinations (the 'unique values' query property will turn on 'select distinct')
then using that query, make a query that groups by pointnumber and counts
surveydate.
"DH" <dana_hartley@(remove)hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Say I have a table with two columns that matter to this example,
[PointNumber] and [SurveyDate]. And here is an example of what the rows
currently look like:

[PointNumber], [SurveyDate]
1,6/27/2001
1,6/27/2001
1,5/31/2001
2,6/27/2001
2,6/30/2001
2,5/31/2001

I want a query that will return PointNumber and the number of surveys (ie. how many different dates are in the table for each point). For the example above, the query should output:

[PointNumber], [NumberOfSurveys]
1,2
2,3

I'd appreaciate any assistance. This seems like it would be simple but I
don't know how to do it.


Nov 12 '05 #3

P: n/a
I don't think so. You'll see she has 2 identical dates for PointNumber 1.
But she wants unique dates.

I think it does need another query. I tried to do it in one query but
couldn't find a way.

Mike

"HSalim" <On******************@msn.com> wrote in message
news:6X*******************@nwrdny01.gnilink.net...
ONE query is all you need
Select PointNumber, Count(*) from
YourTable
group by PointNumber
"dogwalker" <d@g.com> wrote in message
news:30******************@news20.bellglobal.com...
first make a query that returns unique pointnumber, surveydate

combinations
(the 'unique values' query property will turn on 'select distinct')
then using that query, make a query that groups by pointnumber and counts
surveydate.
"DH" <dana_hartley@(remove)hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Say I have a table with two columns that matter to this example,
[PointNumber] and [SurveyDate]. And here is an example of what the rows currently look like:

[PointNumber], [SurveyDate]
1,6/27/2001
1,6/27/2001
1,5/31/2001
2,6/27/2001
2,6/30/2001
2,5/31/2001

I want a query that will return PointNumber and the number of surveys

(ie. how many different dates are in the table for each point). For the example above, the query should output:

[PointNumber], [NumberOfSurveys]
1,2
2,3

I'd appreaciate any assistance. This seems like it would be simple but I don't know how to do it.



Nov 12 '05 #4

P: n/a
Think again.

The requirement was:
the query should output:
[PointNumber], [NumberOfSurveys]
1,2
2,3

This is fulfilled by the query I posted.

If you want to count the number of surveys by points and dates use
Select PointNumber, SurveyDate, Count(*) as NumberOfSurveys
from YourTable
group by PointNumber, SurveyDate

HS

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@pubnews.gradwell.ne t...
I don't think so. You'll see she has 2 identical dates for PointNumber 1.
But she wants unique dates.

I think it does need another query. I tried to do it in one query but
couldn't find a way.

Mike

"HSalim" <On******************@msn.com> wrote in message
news:6X*******************@nwrdny01.gnilink.net...
ONE query is all you need
Select PointNumber, Count(*) from
YourTable
group by PointNumber
"dogwalker" <d@g.com> wrote in message
news:30******************@news20.bellglobal.com...
first make a query that returns unique pointnumber, surveydate combinations
(the 'unique values' query property will turn on 'select distinct')
then using that query, make a query that groups by pointnumber and counts surveydate.
"DH" <dana_hartley@(remove)hotmail.com> wrote in message
news:vn************@corp.supernews.com...
> Say I have a table with two columns that matter to this example,
> [PointNumber] and [SurveyDate]. And here is an example of what the rows > currently look like:
>
> [PointNumber], [SurveyDate]
> 1,6/27/2001
> 1,6/27/2001
> 1,5/31/2001
> 2,6/27/2001
> 2,6/30/2001
> 2,5/31/2001
>
> I want a query that will return PointNumber and the number of
surveys
(ie.
> how many different dates are in the table for each point). For the

example
> above, the query should output:
>
> [PointNumber], [NumberOfSurveys]
> 1,2
> 2,3
>
> I'd appreaciate any assistance. This seems like it would be simple

but I > don't know how to do it.
>
>
>



Nov 12 '05 #5

P: n/a
"HSalim" <On******************@msn.com> wrote in message
news:Il*******************@nwrdny01.gnilink.net...
Think again.

The requirement was:
the query should output:
[PointNumber], [NumberOfSurveys]
1,2
2,3


Well, I just ran your query, with a table of the data that Dana supplied.
And I got:

1,3
2,3

So what am I doing wrong?

Did you get a different result when you ran your query against the sample
data?

Mike
Nov 12 '05 #6

P: n/a
Mike,
I am so sorry. I feel like such a fool.
I was so cock sure of myself that I did not read what you wrote.
You were ofcourse right. The resuts in my query does not match the desired
output.

The correct answer is
SELECT S.PointNumber, count(S.SurveyDate) as SurveyCount
FROM [Select Distinct PointNumber, SurveyDate from surveys]. as S
GROUP BY S.PointNumber;

which is essentially using a subquery that first returns distinct values

HS
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@pubnews.gradwell.ne t...
"HSalim" <On******************@msn.com> wrote in message
news:Il*******************@nwrdny01.gnilink.net...
Think again.

The requirement was:
the query should output:
[PointNumber], [NumberOfSurveys]
1,2
2,3


Well, I just ran your query, with a table of the data that Dana supplied.
And I got:

1,3
2,3

So what am I doing wrong?

Did you get a different result when you ran your query against the sample
data?

Mike

Nov 12 '05 #7

P: n/a
"HSalim" <On******************@msn.com> wrote in message
news:kv******************@nwrdny02.gnilink.net...
Mike,
I am so sorry. I feel like such a fool.
I was so cock sure of myself that I did not read what you wrote.
You were ofcourse right. The resuts in my query does not match the desired output.
No need to apologise. It's easy to misread things.
The correct answer is
SELECT S.PointNumber, count(S.SurveyDate) as SurveyCount
FROM [Select Distinct PointNumber, SurveyDate from surveys]. as S
GROUP BY S.PointNumber;


Why couldn't I get that! I knew there was a simple single query answer to
this. And I knew it was something to do with DISTINCT. I'll get it next time
one of these comes up.

Cheers, Mike
Nov 12 '05 #8

P: n/a
Mike,

Thanks for letting me off the hook so gracefully.

FYI: There is a way to do this in one query - i.e. without requiring a
subquery,
if you use ANSI SQL 92 Syntax. - unfortunately,
a. due to a known problem in Access, you will get an error.
b. it is a database wide option, so you may not want to choose this option.

Anyway, the single query syntax is simply
SELECT surveys.PointNumber,Count(DISTINCT surveys.SurveyDate) AS
CountOfSurveyDate
FROM surveys GROUP BY surveys.PointNumber;

You may find it useful when microsoft gets around to fixing the problem.
see
http://support.microsoft.com/default...roduct=acc2002

HS
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@pubnews.gradwell.ne t...
"HSalim" <On******************@msn.com> wrote in message
news:kv******************@nwrdny02.gnilink.net...
Mike,
I am so sorry. I feel like such a fool.
I was so cock sure of myself that I did not read what you wrote.
You were ofcourse right. The resuts in my query does not match the desired
output.


No need to apologise. It's easy to misread things.
The correct answer is
SELECT S.PointNumber, count(S.SurveyDate) as SurveyCount
FROM [Select Distinct PointNumber, SurveyDate from surveys]. as S
GROUP BY S.PointNumber;


Why couldn't I get that! I knew there was a simple single query answer to
this. And I knew it was something to do with DISTINCT. I'll get it next

time one of these comes up.

Cheers, Mike

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.