group by date range?

Forum Moderators: open

Message Too Old, No Replies

group by date range?

vfaguilera

2:14 pm on Mar 18, 2007 (gmt 0)

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

3:02 am on Mar 19, 2007 (gmt 0)

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

vfaguilera

12:59 am on Mar 23, 2007 (gmt 0)

MySQL 5

Thanks!

aspdaddy

4:22 pm on Mar 25, 2007 (gmt 0)

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