Analyzing dates in a table

[toedipper]

Hello,

PHP and MySql

Thanks to everyone for help on questions I've posted here. Here's
another....

Imagine I have a table containing details of pageviews on a website. To
keep the example simple this table has a colum called 'Date' and imagine
that the earliest date in there is 01/01/04 (01 Jan 04) and each
individual page view has a row.

Can anyone point me in the direction how I could report on this data so
that for example I could display on screen -

Views Today
Views This Week
Views This Year
Views Last Year etc etc

Thanks,

rg.

[ZeldorBlat]

First off, you probably can't use the word 'Date' as a column name.
Aside from that, it isn't too tough:

Hits today:

select sum(pageViews)
from tbl_hits
where Date = curdate()

Hits this week:

select sum(pageViews)
from tbl_hits
where weekofyear(Date ) = weekofyear(curd ate())
and year(Date) = year(curdate())

If you want to count total hits for some arbitrary interval:

select sum(pageViews)
from tbl_hits
where Date between '1/5/2005' and '7/16/2005'

And, if you want to see these broken out by each page, just add
'pageename' to the select list and add a 'group by pagename' at the end
of the query.