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.PageVisited,
Count(tblPageViews.PageVisited) AS CountOfPageVisited
FROM tblPageViews
GROUP BY tblPageViews.PageVisited;

but as soon as I introduce some date criteria:
SELECT DISTINCT tblPageViews.PageVisited,
Count(tblPageViews.PageVisited) AS CountOfPageVisited
FROM tblPageViews
GROUP BY tblPageViews.PageVisited, tblPageViews.StartTime
HAVING tblPageViews.StartTime 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]]

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

You should get rid of the DISTINCT keyword. GROUP BY is already insuring
distinct records...

Count(tblPageViews.PageVisited) AS CountOfPageVisited
FROM tblPageViews
GROUP BY tblPageViews.PageVisited;

but as soon as I introduce some date criteria:
SELECT DISTINCT tblPageViews.PageVisited,
Count(tblPageViews.PageVisited) AS CountOfPageVisited
FROM tblPageViews
GROUP BY tblPageViews.PageVisited, tblPageViews.StartTime
HAVING tblPageViews.StartTime Between #11/1/2004 0:0:1# And
#11/1/2004 23:59:59#;

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,Count(PageVisited) AS CountOfPageVisited
FROM tblPageViews
WHERE StartTime Between #11/1/2004 0:0:1# And #11/1/2004
23:59:59#
GROUP BY PageVisited, StartTime

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?

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

 

[Paxton]

You should get rid of the DISTINCT keyword. GROUP BY is already insuring
distinct records...


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,Count(PageVisited) AS CountOfPageVisited
FROM tblPageViews
WHERE StartTime Between #11/1/2004 0:0:1# And #11/1/2004
23:59:59#
GROUP BY PageVisited, StartTime



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

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