Recently, as part of a task at work, I had to do a count on how many records a table was having per day based also on some other criteria.
In this post, we will focus on the core part of this, the “the count records per day”, since it can be easily reuse for different approaches.
Let’s start!
The Solution
A basic Query to count records grouped by day:
SELECT DATE(`date`) AS record_date,
COUNT(*) AS records
FROM records
WHERE `date` >= '2024-09-17'
AND `date` < '2024-09-19'
GROUP BY DATE(`date`);
Why use DATE() and not DAYOFMONTH()?
DAYOFMONTH() only extracts the day number(1-31), which can be confusing if you’re having data if your range spans multiple months.
By using DATE(), we include the full date (year, month, day) and ensure each record group is unique and accurate.
Making It Dynamic
If you want to count records for the past N days dynamically, adjust the WHERE clause like this:
WHERE `date` >= CURDATE() - INTERVAL 7 DAY
AND `date` < CURDATE()
Replace the 7 with the number of days you need. This could be helpful for tasks like track daily data.
Happy learning and thank you for reading!
If you found this post helpful, hit that ❤️ button and check out my blog for more content: 👉 Code & QA Zone
