CODEWITHSUNDEEP
mysqlsql
Wendy
Wendy

Reputation: 1603

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.

Upvotes: 0

Views: 37

Answers (1)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 3008

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;

Upvotes: 1

Related Questions