Database Reference
In-Depth Information
SELECT
count(*) as PageHits
FROM
log
Figure 10.2 shows the result of that query. Notice that we used an alias to make the out-
put a little bit more meaningful than the column title that the first query showed in
Figure 10.1. If you were to write a script to execute that query on the first page of your web-
site, then it would be a wonderful way of implementing a website page counter. That query
will show all of the hits on all of the pages of our site, as long as you have remembered to
make each page log a hit to it in the database!
If you wanted to implement a page counter for each individual page, then we can use the
same script but this time restrict the search with a WHERE clause like this:
SELECT
count(*) as PageHits
FROM
log
WHERE
webpageID=2
For instance, in our
webpage
table, the
webpageID
of the visitorbook page is 2, so the
script above will count the number of times that an entry has been put in the database for
that page. Executing that query on each page where we want a counter to appear will enable
the same table, log, to be used to generate counters for every page on the site.
Now we have encountered our first aggregate function, it's time to show you something
that makes it even more powerful, the GROUP BY clause.
GROUP BY
GROUP BY is a clause that is added after a select statement that allows you to get extra
functionality from an aggregate function. The GROUP BY clause has to be placed after a
where clause if you include one. You use the GROUP BY clause as follows:
SELECT aggregatefunction(columnname), columns
FROM tablename
GROUP BY columnname
Figure 10.2
Counting all the hits on our website.
Search WWH ::
Custom Search