no one special no one special -4 years ago 91
MySQL Question

mySQL, website statistics: unique daily visits

I have a table

statistics
in database:

CREATE TABLE `statistics` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`address` TEXT NOT NULL,
`ip` TEXT DEFAULT NULL,
PRIMARY KEY (`id`)
)


To obtain daily webpage visits I am using a following query:

SELECT DATE_FORMAT(`timestamp`, "%Y-%m-%d"), COUNT(*)
FROM `statistics`
GROUP BY 1
ORDER BY 1 ASC


How to modify the query to obtain daily visits unique by IP?

Many thanks!

Answer Source

Change it to count distinct IPs like the following

SELECT DATE_FORMAT(`timestamp`, "%Y-%m-%d"), COUNT(DISTINCT `ip`)
FROM `statistics`
GROUP BY 1
ORDER BY 1 ASC

If you want to treat null as a single IP we have to make a slightly different query

SELECT DATE_FORMAT(`timestamp`, "%Y-%m-%d"), COUNT(DISTINCT `ip`) + SUM(ISNULL(`ip`))
FROM `statistics`
GROUP BY 1
ORDER BY 1 ASC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download