MySQL Get rows between months

Question

Hi all,

I'm trying to SELECT the visitors of my site per month for the current year.

For every different IP/user_agent combination there will be added a row per minute. To track the hits and the unique visitors.

My scheme looks like this:

CREATE TABLE `stats` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `domain` varchar(40) NOT NULL,
    `ip` varchar(20) NOT NULL,
    `user_agent` varchar(255) NOT NULL,
    `domain_id` int(11) NOT NULL,
    `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
    `referrer` varchar(400) default NULL,
    PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Now i would like to get all the unique visitors for all the months of a given year.

But i would like to make the unique visitors only unique for 24 hours. So not for the whole month.

It would be possible to just use date >= NOW() - INTERVAL 1 MONTH), but this will jump to 2008 Dec after 2009 Jan. So it should only show the months of a given year.

Are there function to do the same for a month (count the visitors per week, so 4 rows with the first until the fourth week)?

Thanks!

Answer 1

You want to get the number of unique visitors for each site per month? Something like this should work:

SELECT COUNT(*), domain_id, m, y FROM
(
    SELECT ip, user_agent, COUNT(ID) AS hits, domain_id,
        DAY(date) as d, MONTH(date) as m, YEAR(date) as y
    FROM `stats`
    GROUP BY domain_id, ip, d, m, y
) AS tb
GROUP BY tb.m, tb.y

First it groups by day in the subquery, then it groups again by month in the surrounding query.

I'm not entirely sure what your intention was, but you should be able to adapt this technique for your purpose.

(edit: added year component to query)