Is it possible to return the total count of distinct ip_address for every day between a range?

Question

I have a mysql table,

For simplicity's sake assume the following table was used

CREATE TABLE requests(
    id INT NOT NULL auto_increment,
    ipaddress VARCHAR(30) NULL,
    datetime DATETIME NULL,
    PRIMARY KEY(id)
);

Is it possible to return a result set for the count of distinct ip addresses in a 24 hour period, every day between now and and 180 days ago?

datetime               ipaddress
2012-10-25 00:00:00    30
2012-10-24 00:00:00    28
2012-10-24 00:00:00    22
2012-10-23 00:00:00    27
...                    ...

See http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year and count distinct — FoolishSeth, Oct 25 '12 at 06:18

score 3 · Accepted Answer · answered Oct 25 '12 at 06:18

3

try this:

select DATE(`datetime`),count(distinct ipaddress) as ipaddress
from requests
where datediff(NOW(),datetime)<=180
group by DATE(`datetime`)

answered Oct 25 '12 at 06:18

Joe G Joseph

23,518
5
56
58

Just a question, shouldn't you use GETDATE() - instead on NOW()? – TheGeekZn Oct 25 '12 at 06:27
1

@NewAmbition: GETDATE() is a sql server equivalent for NOW() in mysql. The question is tagged with mysql – Joe G Joseph Oct 25 '12 at 06:28

Arun Killu · Answer 2 · 2012-10-25T06:23:15.600

0

  select count(Distinct ipaddress  ) as total from 
 requests where date(datetime)> date_sub(date(datetime),interval 2 day)  
 group by ipaddress

edited Oct 25 '12 at 06:23

answered Oct 25 '12 at 06:17

Arun Killu

13,581
5
34
61

Is it possible to return the total count of distinct ip_address for every day between a range?

2 Answers2