CODEWITHSUNDEEP
mysqlsqlcount
London Smith
London Smith

Reputation: 1659

SQL count for each day

Is it possible to get in one shot a count(*) for each day of the last 15 days like I get for one day:

SELECT count(*) as total from users where `user_date_accountcreated` BETWEEN '2016-10-03 00:00:00' AND '2016-10-03 23:59:59'

To avoid making one SQL request per day.

Thanks.

Upvotes: 2

Views: 152

Answers (3)

Sonam Gurung
Sonam Gurung

Reputation: 127

Try this:

SELECT DATE_FORMAT(user_date_accountcreated, '%Y-%m-%d'), count(*) AS total

    FROM users 

  WHERE

    DATEDIFF(CURDATE(), user_date_accountcreated) BETWEEN 1 AND 15

      GROUP BY DATE_FORMAT(user_date_accountcreated, '%Y-%m-%d')

Upvotes: 0

Marc B
Marc B

Reputation: 360572

Just use mysql's date functions:

SELECT ...
WHERE u_d_a_c BETWEEN (now() - INTERVAL 15 DAY) AND now()
GROUP BY year(u_d_a_c), month(u_d_a_c), day(u_d_a_c)

Upvotes: 2

Chris
Chris

Reputation: 8109

An easy way is to group by date:

SELECT date_format(`user_date_accountcreated`, "%y%m%d"), count(*) as total from users 
where `user_date_accountcreated` BETWEEN '2016-09-18 00:00:00' AND '2016-10-03 23:59:59'
GROUP BY date_format(`user_date_accountcreated`, "%y%m%d")

Upvotes: -1

Related Questions