Single query to get count of individual of past 7 days

Question

I do know that the following conditions will return the total number in the past 7 days

SELECT count(id) FROM registration 
WHERE createdDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()

Is there a single query that I can do to get the past 7 days returning in an array of 7 results of each individual day?

For example:

day 1 - 10
day 2 - 5
day 3 - 9
..
..
..

Answer 1

This will give you the date and the count.

SELECT DATE(createdDate),COUNT(id)
FROM registration
WHERE createdDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY DATE(createdDate)

Alternatively to give a result closer to your example you could use:

SELECT CONCAT("Day ",DATEDIFF(NOW(), createdDate)) AS day,COUNT(id)
FROM registration
WHERE createdDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY DATE(createdDate)

Answer 2

Add a group-by clause:

SELECT count(id), DATE(createdDate)
FROM registration
WHERE createdDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY DATE(createdDate)