mysql group by select with condition value

Question

In my messages table I have following rows for example,

|----|---------|--------------|------|
| id | user_id | message      |status|
|====|=========|==============|======|
| 1  |  2      | msgs 11      |  r   |
|----|---------|--------------|------|
| 2  |  3      | msgs 12      |  r   |
|----|---------|--------------|------|
| 3  |  2      | msgs 13      |  r   |
|----|---------|--------------|------|
| 4  |  3      | msgs 14      |  u   |
|----|---------|--------------|------|

Now, I need to know two things for each user_id

• Whether it has any status u or not.
• How many messages are there

For example, a query like below select user_id, status, count(*) as totalMsg from messages group by user_id Would brought me following rows

| user_id | status| totalMsg |
|=========|=======|==========|
|  2      |   r   |    2     |
|---------|-------|----------|
|  3      |   r   |    2     | 
              ^
              |------> I need this value to be 'u' because user 3 has a message u

My current query doesnt really gurantee that it will look for a u in the status column. Is that possible to do? If so how?

Answer 1

MAX() will work on this since r is the least value based on the lexicographical order.

SELECT  user_ID,
        MAX(status) status,
        COUNT(*) totalMsg
FROM    messages
GROUP   BY user_ID