Wendy Wendy - 5 months ago 9
SQL Question

SQL group by on mutiple columns - MySQL

+----+----------+-------------+-----------+---------------------+---------+---------------------------+
| id | action | object_type | object_id | created_at | user_id | object_title |
+----+----------+-------------+-----------+---------------------+---------+---------------------------+
| 1 | shared | 0 | 1 | 2016-04-22 06:13:16 | 1 | 5 highest buildings |
| 2 | liked | 0 | 1 | 2016-04-22 06:15:02 | 1 | 5 highest buildings |
| 3 | liked | 0 | 1 | 2016-05-21 19:02:18 | 2 | 5 highest buildings |
| 4 | liked | 1 | 3 | 2016-05-21 19:02:21 | 3 | 5 largest bridges |
+----+----------+-------------+-----------+---------------------+---------+---------------------------+


I'm trying to build some thing similar to Facebook's news feed. I mean when multiple users in my friend list like same post, we will show only single result in th news feed saying that friend1, friend2, frnd3, etc., like this xyz post.

From this table I would like to group by on these three columns (
action
,
object_type
,
object_id
) and a list of users for this object.

SELECT *
FROM mytable
GROUP BY action, object_type, object_id


But this gave me some weird results, and I was expecting a list of users. I' really don't have any clue how to do that in this query.

Any help or lead will be appreciated.

Answer

Try this query

SELECT action, object_title,count(user_id) as user_count, group_concat(user_id) as user_ids 
   FROM mytable GROUP BY action, object_type, object_id;