Get count of id for each day and Group by date

Question

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?

Answer 1

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