How to Count Daily Records in MySQL

In this article, we’ll explore different ways to count daily records in MySQL, from the most basic query to more advanced use cases like filtering by date range, formatting dates.

1. Count Records Per Day — The Basic Way

Let’s start with a simple example: we want to count how many sales orders are created each day in our sales_orders table.

The table might look something like this:

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

Note: at the end of this post you will find the download link to the full snippet with the table creation and data to insert.

Query:

SELECT 
    DATE(created_at) AS order_date,
    COUNT(id) AS total_orders
FROM 
    sales_orders
GROUP BY 
    DATE(created_at)
ORDER BY 
    order_date;

Explanation:

• DATE(created_at) extracts the date (without time) from a datetime column.
• GROUP BY DATE(created_at) groups all records by each unique day.
• COUNT(id) counts the number of orders (rows) per day by counting the unique id values, which guarantees we are counting actual orders and not including nulls or other irrelevant rows.

Sample Output:

order_date	total_orders
2025-05-01	5
2025-05-02	3
2025-05-03	2

This is the simplest and most common method to get daily counts.

2. Count Daily Records Within a Date Range

Sometimes, you only want to analyze a specific date range, such as orders from the last 7 days or a particular month.

Query:

SELECT 
    DATE(created_at) AS order_date,
    COUNT(id) AS total_orders
FROM 
    sales_orders
WHERE 
    created_at BETWEEN '2025-05-01' AND '2025-05-02'
GROUP BY 
    DATE(created_at)
ORDER BY 
    order_date;

Explanation:

• WHERE created_at BETWEEN ... restricts the data to a certain period.
• The rest of the query stays the same.

This is useful for dashboards, scheduled reports, or when filtering user activity within a defined time frame.

Sample Output:

order_date	total_orders
2025-05-01	5
2025-05-02	3

3. Alternative Ways to Group by Day

Depending on your use case or formatting needs, there are other ways to group by day:

a) Using DATE_FORMAT() to customize output

SELECT 
    DATE_FORMAT(created_at, '%Y-%m-%d') AS order_day,
    COUNT(*) AS total_orders
FROM 
    sales_orders
GROUP BY 
    DATE_FORMAT(created_at, '%Y-%m-%d')
ORDER BY 
    order_day;

This gives you the same grouping, but with control over the output format, which is useful if you want to display full month names, weekday names, etc.

Sample Output:

order_day	total_orders
2025-05-01	5
2025-05-02	3
2025-05-03	2

b) Grouping by DAY(created_at)

SELECT 
    DAY(created_at) AS day_of_month,
    COUNT(id) AS total_orders
FROM 
    sales_orders
WHERE 
    MONTH(created_at) = 5
GROUP BY 
    DAY(created_at)
ORDER BY 
    day_of_month;

Sample Output:

day_of_month	total_orders
1	5
2	3
3	2

Remember: Without filtering by month/year, grouping by DAY() can mix dates from different months.

4. Count by month

If you need to count all orders per month, regardless of the year you can do it by using group by MONTH().

Query:

SELECT 
    MONTH(created_at) AS order_month,
    COUNT(id) AS total_orders
FROM 
    sales_orders
GROUP BY 
    MONTH(created_at);

Sample Output:

order_month	total_orders
5	10

Note: You can adjust the query further to filter by specific year or additional conditions by using the WHERE clause, for example, let’s consider the year and month when doing the search:

WHERE YEAR(created_at) = 2025 AND MONTH(created_at) = 5

Performance Tips

When working with large tables, there are a few things to keep in mind:

a) Avoid functions on indexed columns in WHERE

-- ❌ Less efficient:
WHERE DATE(created_at) = '2025-05-01'

-- ✅ Better:
WHERE created_at >= '2025-05-01' AND created_at < '2025-05-02'

Calling a function like DATE() on a datetime column prevents MySQL from using indexes efficiently, potentially slowing down queries.

b) Use COUNT(*) vs COUNT(column)

• COUNT(*) counts all rows, including those with NULLs.
• COUNT(column) skips rows where the column is NULL.

In most daily counting cases, COUNT(*) is the safest and clearest choice.

Download the script

Get the code snippet from Github repo or 📥 download directly here

Final Thoughts

Counting records per day is a fundamental SQL task that can be achieved in several ways depending on your needs:

• Use DATE() or DATE_FORMAT() for daily groupings.
• Use WHERE to filter by date ranges.
• Avoid functions in WHERE clauses for better performance.
• Know your grouping level: daily, hourly, monthly — it all depends on context.

Happy learning and thank you for reading!