CODEWITHSUNDEEP
mysql
maxum
maxum

Reputation: 2915

Mysql COUNT by date only part of DATETIME

I have a table like below

 +-----+--------------+---------------------+
 | id  | url          | visit_date          | 
 +-----+--------------+---------------------+
 |  1  + 60           | 2012-06-23 18:15:49 |  
 +-----+--------------+---------------------+
 |  2  + 60&ref=qr    | 2012-06-23 12:15:49 |  
 +-----+--------------+---------------------+
 |  3  + 54           | 2012-06-23 18:15:49 |  
 +-----+--------------+---------------------+
 |  4  + 5            | 2012-06-24 18:15:49 |  
 +-----+--------------+---------------------+
 |  5  + 60           | 2012-06-25 13:15:49 |  
 +-----+--------------+---------------------+
 |  6  + 60           | 2012-06-25 19:15:49 |  
 +-----+--------------+---------------------+

I want to group results my date only. so the result would be if for example I only wanted url with 60.

  +-------------+------------------+
  |visit_date   |count(visit_date) |
  +-------------+------------------+
  |  2012-06-23 |        2         | 
  +-------------+------------------+
  |  2012-06-25 |        2         |
  +-------------+------------------+

I have tried the following but obviously I get every date due to the time difference.

  SELECT visit_time, COUNT( visit_time ) 
  FROM tracking
  WHERE query_string LIKE  '%60%'
  GROUP BY visit_time

is it possible to do this only by the date ignoring the time?

Upvotes: 3

Views: 7620

Answers (2)

John Woo
John Woo

Reputation: 263713

Yes, you can. Just use DATE() function.

SELECT DATE(visit_time), COUNT( visit_time ) 
FROM tracking
WHERE query_string LIKE  CONCAT('%', '60' ,'%')  -- makes readable
GROUP BY DATE(visit_time)

Upvotes: 5

sel
sel

Reputation: 4957

Use this

GROUP BY date(visit_time)

Upvotes: 0

Related Questions