CODEWITHSUNDEEP
mysql
Jitender Yadav
Jitender Yadav

Reputation: 365

MySQL group records based on a column value

Here's my table schema:

Table Schema for Test Table

In the above test table I want to group my records by user_by and message_type only when the message_type = "push_message".

I am trying to achieve it without any sub query, suggestions please.

I tried this

SELECT * FROM `test` group by (CASE WHEN message_type = 'push_message' THEN user_by ELSE id END);

But throws an error in MySQL.

Here's what I want: enter image description here

Upvotes: 0

Views: 3369

Answers (2)

Jitender Yadav
Jitender Yadav

Reputation: 365

Thank you for your responses. After some more research I got it right.

SELECT id,
       user_id,
       user_by,
       message_type
FROM test
GROUP BY IF(message_type = 'push_message', concat(user_by, message_type), id);

Upvotes: 2

Murad Hasan
Murad Hasan

Reputation: 9583

Here is the simple example of GROUP_CONCAT, You may get your desired output after run this/ execute this. The example is with a delimiter ', ' as well, so you can quick understand the things.

The GROUP_CONCAT function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values

SELECT id, user_id,
    GROUP_CONCAT(DISTINCT user_by ORDER BY user_by DESC SEPARATOR ', ')  as user_by
    FROM test 
WHERE message_type = 'push_message'
GROUP BY user_by;

This may help you, if not then please let me know and make a fiddle to understand what actually happen!

If you need to keep other field then please do the GROUP_CONCAT for all these, otherwise the other data go away from the output table.

Upvotes: -1

Related Questions