Access Group by and Count problem

[Paxton]

Hi,

I'm trying to display the total page views per page within a given date
range, but the correct SQL is seemingly beyond me. I get the correct result
with a straightforward Group By and Count clause eg
SELECT DISTINCT tblPageViews.Pa geVisited,
Count(tblPageVi ews.PageVisited ) AS CountOfPageVisi ted
FROM tblPageViews
GROUP BY tblPageViews.Pa geVisited;

but as soon as I introduce some date criteria:
SELECT DISTINCT tblPageViews.Pa geVisited,
Count(tblPageVi ews.PageVisited ) AS CountOfPageVisi ted
FROM tblPageViews
GROUP BY tblPageViews.Pa geVisited, tblPageViews.St artTime
HAVING tblPageViews.St artTime Between #11/1/2004 0:0:1# And #11/1/2004
23:59:59#;

the results change completely. For example, I know the default.asp was
viewed nearly 5000 times within the date range, but it appears twice in the
second resultset, both times with a count of 1.

Incidentally, I have taken into account that the results will differ between
the 2 examples as no criteria restrictions are placed on the first query.

Am I being idiotic in relying on Access's Query Wizard?

TIA

[Bob Barrows [MVP]]

Re: Access Group by and Count problem

Paxton wrote:[color=blue]
> Hi,
>
> I'm trying to display the total page views per page within a given
> date range, but the correct SQL is seemingly beyond me. I get the
> correct result with a straightforward Group By and Count clause eg
> SELECT DISTINCT tblPageViews.Pa geVisited,[/color]

You should get rid of the DISTINCT keyword. GROUP BY is already insuring
distinct records...
[color=blue]
> Count(tblPageVi ews.PageVisited ) AS CountOfPageVisi ted
> FROM tblPageViews
> GROUP BY tblPageViews.Pa geVisited;
>
> but as soon as I introduce some date criteria:
> SELECT DISTINCT tblPageViews.Pa geVisited,
> Count(tblPageVi ews.PageVisited ) AS CountOfPageVisi ted
> FROM tblPageViews
> GROUP BY tblPageViews.Pa geVisited, tblPageViews.St artTime
> HAVING tblPageViews.St artTime Between #11/1/2004 0:0:1# And
> #11/1/2004 23:59:59#;[/color]

This criterion should be put in the WHERE clause, since it has nothing to do
with the aggregated data. Criteria in the WHERE clause are enforced BEFORE
the data is grouped and aggregated. Criteria in the HAVING clause are
enforced AFTER the grouping and aggregation. Whenever possible, you should
put the criteria in the WHERE clause, since this will minimize the number of
records that the grouping engine will work with, improving performance. Your
query should read:

SELECT PageVisited,Cou nt(PageVisited) AS CountOfPageVisi ted
FROM tblPageViews
WHERE StartTime Between #11/1/2004 0:0:1# And #11/1/2004
23:59:59#
GROUP BY PageVisited, StartTime

[color=blue]
>
> the results change completely. For example, I know the default.asp
> was viewed nearly 5000 times within the date range, but it appears
> twice in the second resultset, both times with a count of 1.
>
> Incidentally, I have taken into account that the results will differ
> between the 2 examples as no criteria restrictions are placed on the
> first query.
>
> Am I being idiotic in relying on Access's Query Wizard?[/color]

No, just learn to use it correctly. You can choose "Where" in the Total row
in the grid, instead of one of the aggregation functions (Min, Sum, etc).
That's what you should choose in the StartTime column in the grid (You'll
need to create a second column containing StartTime, since you are both
filtering and grouping by it. To tell you the truth, I'm not sure why you
are grouping by StartTime ...).

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Paxton]

Re: Access Group by and Count problem


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:OjiFpgSwEH A.384@tk2msftng p13.phx.gbl...[color=blue]
> Paxton wrote:[color=green]
>> Hi,
>>
>> I'm trying to display the total page views per page within a given
>> date range, but the correct SQL is seemingly beyond me. I get the
>> correct result with a straightforward Group By and Count clause eg
>> SELECT DISTINCT tblPageViews.Pa geVisited,[/color]
>
> You should get rid of the DISTINCT keyword. GROUP BY is already insuring
> distinct records...
>[color=green]
>> Count(tblPageVi ews.PageVisited ) AS CountOfPageVisi ted
>> FROM tblPageViews
>> GROUP BY tblPageViews.Pa geVisited;
>>
>> but as soon as I introduce some date criteria:
>> SELECT DISTINCT tblPageViews.Pa geVisited,
>> Count(tblPageVi ews.PageVisited ) AS CountOfPageVisi ted
>> FROM tblPageViews
>> GROUP BY tblPageViews.Pa geVisited, tblPageViews.St artTime
>> HAVING tblPageViews.St artTime Between #11/1/2004 0:0:1# And
>> #11/1/2004 23:59:59#;[/color]
>
> This criterion should be put in the WHERE clause, since it has nothing to
> do
> with the aggregated data. Criteria in the WHERE clause are enforced BEFORE
> the data is grouped and aggregated. Criteria in the HAVING clause are
> enforced AFTER the grouping and aggregation. Whenever possible, you should
> put the criteria in the WHERE clause, since this will minimize the number
> of
> records that the grouping engine will work with, improving performance.
> Your
> query should read:
>
> SELECT PageVisited,Cou nt(PageVisited) AS CountOfPageVisi ted
> FROM tblPageViews
> WHERE StartTime Between #11/1/2004 0:0:1# And #11/1/2004
> 23:59:59#
> GROUP BY PageVisited, StartTime
>
>[color=green]
>>
>> the results change completely. For example, I know the default.asp
>> was viewed nearly 5000 times within the date range, but it appears
>> twice in the second resultset, both times with a count of 1.
>>
>> Incidentally, I have taken into account that the results will differ
>> between the 2 examples as no criteria restrictions are placed on the
>> first query.
>>
>> Am I being idiotic in relying on Access's Query Wizard?[/color]
>
> No, just learn to use it correctly. You can choose "Where" in the Total
> row
> in the grid, instead of one of the aggregation functions (Min, Sum, etc).
> That's what you should choose in the StartTime column in the grid (You'll
> need to create a second column containing StartTime, since you are both
> filtering and grouping by it. To tell you the truth, I'm not sure why you
> are grouping by StartTime ...).
>[/color]

I was grouping by StartTime because, initially, the Wizard told me I had to
include it in my aggregate function. I didn't want to - and your
explanation has shown me how to get rid of it, and what I was doing wrong.
I got rid of the first (Groupby) StartTime column, retained your (Where)
StartTime column and got the results I wanted. Many thanks for your help,
Bob.

Paxton