CODEWITHSUNDEEP
mysql
no one special
no one special

Reputation: 1764

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!

Upvotes: 1

Views: 43

Answers (2)

user1898027
user1898027

Reputation: 340

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

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30819

You can add IPin SELECT and GROUP BY, e.g.:

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

Upvotes: 1

Related Questions