Show correct "most visited" date

date, group-by, mysql, sql

Solution

This will show dates having the same highest number of total visits

SELECT DATE(date_firstvisit) Dates, COUNT(*) totalCount
FROM visitors
GROUP BY DATE(date_firstvisit)
HAVING COUNT(*) = 
(
  SELECT MAX(s)
  FROM(
  SELECT COUNT(*) s
  FROM visitors
  GROUP BY DATE(date_firstvisit))s
)

- SQLFiddle Demo

- SQLFiddle Demo (with duplicates)

Problem

I have some problems to show the most visited date. Here's how it looks like in the database: ``` date_firstvisit 2012-11-25 15:49:16 2012-11-21 17:42:40 2012-11-21 15:36:04 2012-11-20 07:39:11 2012-11-19 09:19:36 2012-11-15 23:03:24 2012-11-15 22:57:47 2012-11-15 03:10:20 2012-11-15 03:10:14 ``` This SQL query should print `2012-11-15` but it only print `0000-00-00`: ``` SELECT date_firstvisit FROM visitors GROUP BY DATE(date_firstvisit) ORDER BY COUNT(date_firstvisit) DESC LIMIT 1 ``` If I replace `SELECT date_firstvisit FROM visitors` to `SELECT * FROM visitors` it prints `2012-11-28`. This is of course wrong! I don't know what's wrong with my SQL query so I'm asking you now, have I missed something? EDIT This code shows how I prints the data to my website. According to njk this SQL is correct but it's just keep printing `0000-00-00`. ``` $get_mostactive_date = $sql->query("SELECT DATE(date_firstvisit), COUNT(date_firstvisit) FROM visitors GROUP BY DATE(date_firstvisit) ORDER BY COUNT(date_firstvisit) DESC LIMIT 1"); $mostactive_date = $get_mostactive_date->fetch(PDO::FETCH_ASSOC); echo $mostactive_date['date_lastactive']; ``` Thanks in advance.

Original source