Apply Logic In a MySQL Column with IF or Case

Question

I face this problem and I can't think of a way to proceed. So, I have a table let's say with 3 entries with the same id and a column named status that has for the first entry the value active, for the second entry the value inactive and paused for the last. Keep in mind that the logic should be applied in a join since the query that already runs is quite big.

So I a want to create a subquery to say if there is at least one status active return status active for this id, if there is no status active but there is one status paused return status paused, otherwise return statusinactive.

I tried already to use IF() with group_concat() for all the statuses and also a CASE statement but I don't get the required result.

Any guess on that? P.S: I am using MySQL 5.6

Answer 1

You may use conditional aggregation here:

SELECT
    id,
    CASE WHEN SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) > 0
         THEN 'active'
         WHEN SUM(CASE WHEN status = 'paused' THEN 1 ELSE 0 END) > 0
         THEN 'paused'
         ELSE 'inactive' END AS status
FROM yourTable
GROUP BY id;