group by date range?

[vfaguilera]

I have a log table that holds hits like this:

Table Log
id ¦ date
94 ¦ 2007-03-01
94 ¦ 2007-03-01
94 ¦ 2007-03-07
94 ¦ 2007-03-07
94 ¦ 2007-03-07
94 ¦ 2007-03-12
94 ¦ 2007-03-18
94 ¦ 2007-03-18
94 ¦ 2007-03-20

Table items
id ¦ Name
94 ¦ Item 1

I need to build a report that would output:
(assuming today is the 20th)

Name ¦Today ¦ This week ¦ This month
Item 1¦ 1 ¦ 3 ¦ 9

is it possible to get that information using one query? I can easily get it with 3, but if I want to show the report for more than one id those queries would multiply and the report would become slow as time passes by.

any ideas?

[bmcgee]

What kind of database are you using? Syntax will depend on the database.

[vfaguilera]

MySQL 5

Thanks!

[aspdaddy]

in SQL Server you would use several CASE WHEN's to create a DIY Pivot/Crosstab. This code gives you the general idea;

SELECT
SUM(CASE WHEN date BETWEEN a and b THEN 1 ELSE 0 END AS Today),
SUM(CASE WHEN date BETWEEN c and d THEN 1 ELSE 0 END AS [This Month])
FROM table