Neil Rupert Neil Rupert - 14 days ago 5
MySQL Question

Count occurrences across each row in mySQL

I have been racking my brain on how to count how often a string occurs across each row, and ultimately update another column.

If I have the questions table that contains a sponsors answers for each question in a record:


+-------------+-----+-----+-----+-----+-----+
| response_id | q1 | q2 | q3 | q4 | q5 |
+-------------+-----+-----+-----+-----+-----+
| 1 | Yes | N/A | Yes | No | Yes |
| 2 | No | Yes | No | Yes | N/A |
| 3 | Yes | No | No | N/A | Yes |
+-------------+-----+-----+-----+-----+-----+


Is it possible to do this:


+-------------+-----+-----+-----+-----+-----+-----+----+-----+
| response_id | q1 | q2 | q3 | q4 | q5 | Yes | No | N/A |
+-------------+-----+-----+-----+-----+-----+-----+----+-----+
| 1 | Yes | N/A | Yes | No | Yes | 2 | 1 | 1 |
| 2 | No | Yes | No | Yes | N/A | 2 | 1 | 1 |
| 3 | Yes | No | No | N/A | Yes | 1 | 1 | 2 |
+-------------+-----+-----+-----+-----+-----+-----+----+-----+


I am importing this data from an csv to a Laravel application. It if's not possible, is there a way I can rearrange this data to get to the end result.

Thanks!

Answer

You can use the update query like the one below:

update responses set Yes = IF(q1='Yes',1,0) 
        + IF(q2='Yes',1,0)
        + IF(q3='Yes',1,0)
        + IF(q4='Yes',1,0)
        + IF(q5='Yes',1,0);

This will update the existing rows (here's SQL Fiddle). For the new rows, you can write something in the application itself, to caculate these values before a row is inserted.

Comments