MySQL counting occurrence of values in a field

Question

I've got a MySQL table containing 115 fields like these:

v1q1  v1q2  v2q1  v2q2  v3q1  v3q3 (...)

The records all contain one of the value 0, 1, 2, 3 or 4.

Now I need to count for every field the number of records containing a 0, the number of records containing a 1, the number of records containing a 2,... (until 4).

How I can accomplish this in a rather efficient way?

Answer 1

You can use the IF statement and doing a sum like this:

SELECT
 SUM(IF(v1q1=0, 1, 0)) as cnt0, 
 SUM(IF(v1q1=1, 1, 0)) as cnt1,
 SUM(IF(v1q1=2, 1, 0)) as cnt2,
 SUM(IF(v1q1=3, 1, 0)) as cnt3,
 SUM(IF(v1q1=4, 1, 0)) as cnt4 
FROM `myTable`

Answer 2

You can't do that in any efficient way. The layout of the database itself is inefficient, and you can't overcome that with any kind of query.

Patrick showed you how to do it for one single field in the table. Now make a query that contains that 115 times... As you see, that is not efficient.

You should have the values in the table where the data that you now have in the field names is where data belongs; in the fields:

id v q value
-------------
1  1 1 2
1  1 2 4
1  2 1 0
1  2 2 1
1  3 1 3
1  3 3 2
2  1 1 0
2  1 2 1
2  1 2 0
2  2 1 4
...

Now that you have all the data as actual data, you can get the information efficiently:

select v, q, value, count(*)
from TheTable
group by v, q, value