Counting Daily Records in MySQL – A Step-by-Step Guide

In this post, you’ll learn how to create a MySQL script that counts how many records were added per day to your database. This came up during a past project, where I had to compare the count of daily records from a system versus a manual count done by the sales team.

Let’s say we have a Sales Orders table that records every purchase a customer makes on our website. The table might look something like this:

CREATE TABLE sales_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255),
    order_date DATETIME
);

Sample Data

id	customer_name	order_date
1	Alice	2025-04-20 14:22:00
2	Bob	2025-04-20 16:10:23
3	Charlie	2025-04-21 09:45:12
4	Diana	2025-04-21 12:10:11
5	Evan	2025-04-21 20:00:55

Now, we want to count the total orders placed each day. Here’s the query:

SELECT DATE(order_date) AS thedate,
       COUNT(*) AS count
FROM sales_orders
GROUP BY thedate
ORDER BY thedate ASC;

What the Output Looks Like

thedate	count
2025-04-20	2
2025-04-21	3

Breaking down the query

DATE(order_date) AS thedate

This extracts only the date part from the full order_date (which includes both date and time). This helps us group all orders from the same day together, ignoring the exact time.

COUNT(*) AS count

This counts how many records (or orders) exist for each day. The * means it counts all rows, but if you need to count specific fields (e.g. Only orders over a certain amount), you can tweak this based on your needs.

GROUP BY thedate

Here’s the magic: GROUP BY groups the records by date so that MySQL applies the COUNT() function to each group.

ORDER BY thedate ASC

This makes sure the results are in chronological order. If you want to display the most recent records first, simply change ASC to DESC.

Customization Tips

Here are some ways to tweak this query for your needs:

Filter by a Date Range

If you want to count records within a specific date range, use the WHERE clause:

WHERE order_date BETWEEN '2025-04-01' AND '2025-04-21'

Filter by Customer

To focus on a specific customer’s orders, use a WHERE clause like this:

WHERE customer_name = 'Alice'

Only Weekdays

If you only care about weekdays (e.g., Monday to Friday), use:

WHERE DAYOFWEEK(order_date) BETWEEN 2 AND 6

Real-World Use Cases

Here’s how this query might be used in practice:

• Sales & Revenue Reporting: Track how many orders were placed each day. This is useful for comparing sales to goals or forecasts.
• Customer Engagement: Track daily sign-ups or logins to understand user engagement trends.
• Productivity & Support Metrics: Monitor how many customer service tickets are submitted each day, or how many issues are reported for a software product.
• Marketing Campaigns: Analyze daily clicks or conversions during a promotional period.

Common Mistakes to Avoid

• Not extracting the date part: If you forget to use DATE() on your datetime field, each record will be treated as a unique timestamp, not as a group of records from the same day.

• Time Zone Differences: If your application runs across multiple time zones, make sure your order_date field is normalized (e.g., in UTC) to avoid incorrect grouping.

Final Thoughts

This kind of query is simple but powerful for building reports, tracking business metrics, or gaining insights from your data. you’re building dashboards, daily reports. It’s a powerful tool in your SQL toolkit for working with time-based data.

And now, you’re not only ready to use it, but you also understand why it works the way it does.

📥 Download the example script here

Are you ready for more? Check out this 👉 Count Daily Records in MySQL Within a Date Range – A Step-by-Step Guide