Count the number of fields that are not empty in MySQL

Question

I have a database of users where they can send messages to other users (up to four) and the ID of the message they sent is in their user row.

Name    | Email       | Msg1 | Msg2 | Msg3 | Msg4
--------+-------------+------+------+------+-----
Pez     | [email protected]   |  1   |  55  |  42  |  5 
Steve   | [email protected] |  0   |   0  |   0  |  0 
Leon    | [email protected] |  3   |   0  |   3  |  5 

How in a MySQL query can I get the amount of those message rows that are not empty or not equal to 0, allowing me to order by that? So it would return

Pez   | 4 Mesasges
Leon  | 3 Messages
Steve | 0 Messages

Im my mind something like:

order by count(!empty(msg1)+!empty(msg2)+!empty(msg3)+!empty(msg4)) 

Answer 1

Your best bet would be to normalize your database as follows:

Table users:    (user_id, name, email)
Table messages: (user_id, msg_number, msg_code)

The users table would have user_id as a primary key, while the messages table would have a composite primary key on (user_id, msg_number). The msg_number field would represent the 1-4 message number (the number you represented in your column names).

The composite primary key would guarantee that each user can only have one message for each msg_number. You may also want to create a check constraint so that msg_number is always <= 4. Finally you may want to define user_id in the messages table as a foreign key to the users table.

Note that if a user did not send a message, you should not have a row in the messages table with msg_code = 0. Instead, you should simply omit the row.

With normalized tables, counting the number of messages for each user would be as easy as:

SELECT    u.name, COUNT(m.msg_number) as num_of_messages
FROM      users u
JOIN      messages m ON (m.user_id = u.user_id)
GROUP BY  u.user_id;

Answer 2

With normalized tables it could be as simple as

SELECT Count(*) FROM messages WHERE userid=47

But with your schema try something like

SELECT
  name,
  (msg1<>0) + (msg2<>0) + (msg3<>0) + (msg4<>0) as cMessages
FROM
  foo  

edit: Vincent Ramdhanie's answer using if() doesn't rely on MySQL's behaviour to use 0 as false and 1 as true as result of a comparison. I would prefer that.

Answer 3

 select 
     if(msg1 = 0, 0, 1) +
     if(msg2 = 0, 0, 1) +
     if(msg3 = 0, 0, 1) +
     if(msg4 = 0, 0, 1) as NumMsg,Name
 from table

Answer 4

As others have said the right way to solve the problem is to normalize your database.

You can get the counts from the current schema - but its a bit messy:

SELECT name, (SIGN(msg1)+SIGN(msg2)+SIGN(msg3)+SIGN(msg4))
FROM yourtable
ORDER BY (SIGN(msg1)+SIGN(msg2)+SIGN(msg3)+SIGN(msg4)) DESC;

C.