CODEWITHSUNDEEP
mysqlgroup-by
Airikr
Airikr

Reputation: 6436

Get the highest amount of visitors grouped by date

I want to group every date that it is in the database and get the highest amount to display it on the website. I have tried to use this SQL; SELECT COUNT(date_visited) FROM visitors GROUP BY date_visited but it only shows 1.

The database looks like this:

CREATE TABLE IF NOT EXISTS `visitors` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ipaddress` text NOT NULL,
  `page` text NOT NULL,
  `page_get` text NOT NULL,
  `date_visited` datetime NOT NULL,
  `date_lastactive` datetime NOT NULL,
  `date_revisited` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

How can I solve this problem?

Thanks in advance.

Upvotes: 1

Views: 78

Answers (2)

Mark Byers
Mark Byers

Reputation: 839234

Andomar's answer is right that you are grouping by the datetime instead of just the date.

In addition, it seems you are trying to find the date that occurs most often in your table. To do this you can try the following: 

SELECT DATE(date_visited)
FROM visitors
GROUP BY DATE(date_visited)
ORDER BY COUNT(*) DESC
LIMIT 1

Upvotes: 3

Andomar
Andomar

Reputation: 238296

If you're grouping on a datetime column, you typically get a group for every point in time. To group by day instead, you could cast datetime to date:

select  date(date_visited)
,       count(*) 
from    visitors 
group by
        date(date_visited)
order by
        count(*) desc
limit   1

Upvotes: 1

Related Questions