CODEWITHSUNDEEP
mysqlsqldategroup-by
Airikr
Airikr

Reputation: 6436

Show correct "most visited" date

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.

Upvotes: 3

Views: 152

Answers (2)

John Woo
John Woo

Reputation: 263803

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
)

Upvotes: 5

Kermit
Kermit

Reputation: 34063

You are missing the DATE function around date_firstvisit in your SELECT. You also need an alias to handle the use of functions with PDO.

Assuming MySQL:

SELECT DATE(date_firstvisit) AS firstvisit, COUNT(date_firstvisit) AS count
FROM visitors
GROUP BY DATE(date_firstvisit)
ORDER BY COUNT(date_firstvisit) DESC
LIMIT 1

See it in action

Upvotes: 3

Related Questions