CODEWITHSUNDEEP
mysqlsql
user2095165
user2095165

Reputation: 195

MySql Select and count number of data per day

I have a following table with columns:

id    |    data    |    date

How do I query all the data in a date range from 1 to 30 days and count all data per day?

I tried something but it groups by seconds also

Select count(data) as data 
From table
Where date between '2013-12-02 00-00-00' and '2014-01-02 23:59:59'
group by date

I want to achieve 

day 1  |  day 2  |  day  | day 4  | . . .
 3     |   4     |   2   |    5   | . . .

Upvotes: 1

Views: 10021

Answers (3)

Daniel Nyerere
Daniel Nyerere

Reputation: 11

Seems like you want a query like one below:

SELECT 
CASE date_format(date ,"%W")
WHEN 'Monday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Monday' ) ,0) ELSE 0
END AS 'Monday',
CASE date_format(date ,"%W")
WHEN 'Tuesday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Tuesday' ) ,0) ELSE 0
END AS 'Tuesday',
CASE date_format(date ,"%W")
WHEN 'Wednesday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Wednesday' ) ,0) ELSE 0
END AS 'Wednesday',
CASE date_format(date ,"%W")
WHEN 'Thursday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Thursday' ) ,0) ELSE 0
END AS 'Thursday',
CASE date_format(date ,"%W")
WHEN 'Friday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Friday' ) ,0) ELSE 0
END AS 'Friday',
CASE date_format(date ,"%W")
WHEN 'Saturday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Saturday' ) ,0) ELSE 0
END AS 'Saturday',
CASE date_format(date ,"%W")
WHEN 'Sunday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Sunday' ) ,0) ELSE 0
END AS 'Sunday'
FROM YOUR_TABLE

Upvotes: 0

Farid Efendi
Farid Efendi

Reputation: 41

Try the following

SELECT DATE_FORMAT(date, '%Y-%m-%d') AS the_date
     , COUNT(*) AS count 
  FROM table 
 WHERE date BETWEEN DATE_FORMAT('2014-11-28', '%Y-%m-%d') AND DATE_FORMAT('2014-12-05', '%Y-%m-%d') 
 GROUP 
    BY the_date

Upvotes: 4

mameyugo
mameyugo

Reputation: 298

SELECT @count:=0;
SELECT CONCAT('day ',@count+1) as day, COUNT(*), date 
FROM date between
  '2013-12-02 00-00-00'
AND
  '2014-01-02 23:59:59')
GROUP BY date

Upvotes: 0

Related Questions