Count where and group by but show the latest id of each group

user_16970233 0
Viewed 30

I have chat_notif table like this :

id order_id chat_id sent_to
1 order-001 1 2
2 order-002 2 1
3 order-001 3 2
4 order-001 4 2
5 order-002 5 1
6 order-003 6 2
7 order-003 7 2
8 order-003 8 2
9 order-001 9 2
10 order-002 10 1

What i need is to get count but with group by order_id where sent_to is 2:

SELECT *, count(id) as count FROM chat_notif WHERE sent_to=2 GROUP BY order_id
id order_id chat_id sent_to count
1 order-001 1 2 4
6 order-003 6 2 3

From the table above, the count is right. But what i need is to get the last id of each group so it should be like this :

id order_id chat_id sent_to count
9 order-001 9 2 4
8 order-003 8 2 3

How do i do it then?

mysql
1 Answers

We can find the last values with MAX and use that to aggregate the values into the query.

If we want to include other fields along with the count() function we need to either include them in the GROUP BY or use another aggregate function such as SUM or AVERAGE.

Luckily, you mention you only want the last id of each group. MAX works well for this purpose and counts as an aggregate function, so we're in luck.

Note, this assumes the fields "id" and "chat_id" are numeric types such as integers so that they work with the MAX function. You may run into issues with other (non-numeric) data types.

SELECT MAX(id) as id, order_id, MAX(chat_id) as chat_id, sent_to, count(id) as count 
FROM chat_notif 
WHERE sent_to=2 
GROUP BY order_id;
    
+------+-----------+---------+---------+-------+
| id   | order_id  | chat_id | sent_to | count |
+------+-----------+---------+---------+-------+
|    9 | order-001 |       9 |       2 |     4 |
|    8 | order-003 |       8 |       2 |     3 |
+------+-----------+---------+---------+-------+
2 rows in set (0.00 sec)
user_171469000
Related