Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get count of id for each day and Group by date

Currently, I have a query that looks like this:

SELECT send_id, count(id) FROM `table1`
WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY send_id;

This returns a result that looks like this:

|   send_id   |   count(id)   |
-------------------------------
|    00123    |       32      |  
|    00234    |       12      | 
|    00567    |      100      | 
|    00890    |       07      | 
-------------------------------

I want to add the date and get the count for each send_id for each day.

I wrote this query:

SELECT DATE(date) as 'date', send_id, count(id) FROM `table1`
WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY date, send_id;

I use DATE(date) because the column date is a datetime. I don’t need the time, just the date.

I am getting results that look like this:

|     date    |   send_id   |   count(id)   |
---------------------------------------------
| 2024-09-01  |    00123    |       03      |  
| 2024-09-01  |    00123    |       10      | 
| 2024-09-01  |    00567    |       20      | 
| 2024-09-01  |    00567    |       05      | 
---------------------------------------------

The results I was looking for should be like this:

|     date    |   send_id   |   count(id)   |
---------------------------------------------
| 2024-09-01  |    00123    |       13      |  
| 2024-09-01  |    00567    |       25      | 
---------------------------------------------

Because send_id had a total id count of 13 on 2024-09-01, but it’s breaking it off into 2 rows.

The id is the auto_incremented field.

I think it’s because of the datetime, but I’m not sure.

How can I fix the query so that it gets the id count of each send_id for each day?

Tags:

5

Answers


  1. 0

    Just adding comment as answer. Check out your GROUP BY, where you need to also group by date(date).

    GROUP BY DATE(DATE), SEND_ID
    Login or Signup to reply.
  2. 0

    you can use try_cast to convert the data into date_type

    SELECT 
    TRY_CAST(date AS DATE) AS date,
    send_id,
    COUNT(id) AS count_id
FROM table1
WHERE date BETWEEN '2024-09-01' AND '2024-09-30'
GROUP BY TRY_CAST(date AS DATE), send_id
    Login or Signup to reply.
  3. 0

    When you use GROUP BY date it’s grouping by the date column in the table, not the date alias that you assigned to DATE(date).

    You can avoid this problem by using a different name for the alias:

    SELECT DATE(date) AS txn_date, , send_id, count(id) 
FROM `table1`
WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY txn_date, send_id;
    Login or Signup to reply.
  4. 0

    I’d recommend a derived table, to make the date to DATE(date) conversion in advance, then GROUP BY its result. (Very handy trick to avoid repeating complex group by expressions.)

    SELECT date, send_id, count(id)
FROM
(
    SELECT DATE(date) as 'date', send_id, id  FROM `table1`
    WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%'
) dt
GROUP BY date, send_id
    Login or Signup to reply.
  5. 0

    Given you have datetime columns, this WHERE clause will perform so much better:

    WHERE date >= '2024-09-01' AND date < '2024-10-01'

    Thanks to cultural/internationalization issues, treating dates as strings is much slower and more error-prone than we’d like to believe. It’s something to avoid.

    Using the LIKE '2024-09-01%' expression forces the database to implicitly do this slower conversion from datetime to a string type for every row in the table, even rows that won’t be used. And making this conversion means the converted values no longer match values used with any index you might have had, making the index worthless for this query.

    Whenever possible you want to write your conditional expressions to avoid mutating the values stored in the table.

    In the case of dates, this commonly means writing two expressions forming a half-open range with an exclusive upper bound for the day after the close of the range.

    Put it all together like this:

    SELECT DATE(date) as 'date', send_id, count(id)
FROM `table1`
WHERE date >= '2024-09-01' AND date < '2024-10-01'
GROUP BY DATE(DATE), SEND_ID

    As a bonus, for month ranges like this you no longer have to worry about finding the correct last day of the month. Instead you will always use the first day of the month following.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search