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?
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`
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
