SQL count and group only returns first row for each group

My table:

+----------+---------------------+  
| bonus_id | created             |  
+----------+---------------------+  
|  11      | 2020-04-28 19:50:13 |  
|  12      | 2020-04-29 00:00:00 |  
|  13      | 2020-04-30 19:55:58 |  
|  14      | 2020-04-29 19:56:07 |  
|  15      | 2020-04-30 19:56:18 |  
|  16      | 2020-04-29 20:16:21 |  
+----------+---------------------+

This is what I’m trying to get

2020-04-30 - Count: 2
13
15
2020-04-29 - Count: 3
12
14
16
2020-04-28 - Count: 1
11

This is what I’m getting:

2020-04-30 - Count: 2
13
2020-04-29 - Count: 3
12
2020-04-28 - Count: 1
11

My code:

SELECT DATE(created) as created, COUNT(*) as count, bonus_id FROM test WHERE account_id=? AND user_id=? GROUP BY DATE(created) DESC

I’m getting the correct order with all the outpounts when i use ORDER BY and not GROUP/COUNT, but then I’m not getting the count. Don’t understand why I’m only getting the first row for each date.

Answer

If you want to add a column with the count to each row, use window functions:

select t.*, count(*) over (partition by date(created)) as day_cnt
from test t;

If you want an aggregated list, then I’ll assume you are using MySQL (an old version) or SQLite because these do not generate a syntax error. In that case, you can use group_concat():

select date(created), count(*), group_concat(bonus_id) as bonus_ids
from test
group by date(created);

Advertisement

Answer