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?
>Solution :
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