Count the number of fields that are not empty in MySQL

Question

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

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

How can I get the number of message lines in a MySQL query that are not empty or not equal to 0, which allows me to order this? So he will return

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

It seems to me something like:

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

Answer 1

With normalized tables, it can be as simple as

 SELECT Count(*) FROM messages WHERE userid=47 

But with your circuit try something like

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

edit: Vincent Ramdhanie responds with if () does not rely on MySQL behavior to use 0 as false and 1 as true as the result of the comparison. I would prefer that.

Answer 2

  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 3

It would be best 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 the primary key, and the messages table would have a composite primary key at (user_id, msg_number) . The msg_number field will represent message number 1-4 (the number you specified in the column names).

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

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

With normalized tables, counting the number of messages for each user will be as simple 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 4

As others have said, the normal solution to the problem is to normalize the database.

You can get the counts from the current schema, but this is a bit dirty:

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

FROM.