One query, multiple date ranges in SQL

Hi all,

We're using the Pear DBI to interface with multiple DBs, so I'm looking for a regular SQL query here. I want to query a timestamped table for the number of records in each week of a year. Presently, this takes 52 queries. Is there a way to do this in one query?

Format:
__________________
Log_ID int
Session_ID int
Page_ID int
DateStamp datetime



Thanks

Statik

Hi Statik!

I doubt it is possible through straight SQL with only the fields you've stated. I think you would need to add WeekNo, either as a computed column (wonder how many databases support this?) or through defining a view. Using either of these, it should be easy to write the query.

Making 52 queries sounds like the most time consuming and error prone way to do this - and then you miss week 53 every 6 years or so.

Hi,

Would something like this work:

select week, count(*)
from
(
select extract(week from timestamp) as week
from yourtable
) as temp
group by week;

there's a wee problem here in that not every database supports a WEEK() function

you'll also need an integers table, but it's a snap to create and you only have to do it once