assuming that I have a db that have an horizontal structure like this:
ID | NAME | DATA1 | DATA2 | DATA3 | DATA4 | DATA5 | DATA6 | DATA7 1 | mmm | 0 | 1 | 0 | 3 | 5 | 1 | 0 2 | bbb | 0 | 0 | 0 | 1 | 0 | 1 | 1
the informations are the data fields and I would like to count all the times that a certain discriminant, such as "is more than 0"
the way I thought it is loop trought all the fields, and count, or
DATA field, so
7 queries... anyone has another idea?
in this case the result, so
"count every DATA field with a value over 0" would be = 7
There's no built-in syntax that would allow you to refer to a set of columns dynamically, i.e. without explicitly naming them. If you want dynamicity, you'll need to query metadata to grab the required column names, then build the final query dynamically.
But before that you'd still need to have an idea how exactly the dynamic query should go about performing the job itself. So, you'll first to need to solve the problem on a <em>finite</em> column set.
There's more than one way to solve this problem. The <a href="https://stackoverflow.com/a/15090967/297408" rel="nofollow">method suggested by @bluefeet</a> is probably one of the clearer as well as less efficient ones. You could try at least two alternatives:<ol><li>
Count every column separately using conditional aggregation and add up all the results in one expression:
SELECT COUNT(DATA1 > 0 OR NULL) + COUNT(DATA2 > 0 OR NULL) + COUNT(DATA3 > 0 OR NULL) + COUNT(DATA4 > 0 OR NULL) + COUNT(DATA5 > 0 OR NULL) + COUNT(DATA6 > 0 OR NULL) + COUNT(DATA7 > 0 OR NULL) AS TOTAL FROM yourtable ;
OR NULL trick is explained <a href="https://stackoverflow.com/questions/5011239/why-do-i-need-or-null-in-mysql-when-counting-rows-with-a-condition" title="Why do I need “OR NULL” in MySQL when counting rows with a condition" rel="nofollow">here</a>.)
DATA columns using a cross join to a virtual table, then apply the condition to the unpivoted column:
SELECT COUNT(*) AS TOTAL FROM ( SELECT CASE s.col WHEN 'DATA1' THEN DATA1 WHEN 'DATA2' THEN DATA2 WHEN 'DATA3' THEN DATA3 WHEN 'DATA4' THEN DATA4 WHEN 'DATA5' THEN DATA5 WHEN 'DATA6' THEN DATA6 WHEN 'DATA7' THEN DATA7 END AS DATA FROM yourtable CROSS JOIN ( SELECT 'DATA1' AS col UNION ALL SELECT 'DATA2' UNION ALL SELECT 'DATA3' UNION ALL SELECT 'DATA4' UNION ALL SELECT 'DATA5' UNION ALL SELECT 'DATA6' UNION ALL SELECT 'DATA7' ) s ) s WHERE DATA > 0 ;
(In a way, this is similar to @bluefeet's suggestion, it just doesn't employ any UNIONs.)</li> </ol>Answer2:
Since your data is not normalized you should <em>unpivot</em> the data to get the result. MySQL does not have an unpivot function so you can use a
UNION ALL query to convert your columns into rows. Once the data is in the rows, then you can easily count the number of values. I would use something similar to this:
select count(*) total from ( select id, name, 'data1' col, data1 as value from yourtable union all select id, name, 'data2' col, data2 as value from yourtable union all select id, name, 'data3' col, data3 as value from yourtable union all select id, name, 'data4' col, data4 as value from yourtable union all select id, name, 'data5' col, data5 as value from yourtable union all select id, name, 'data6' col, data6 as value from yourtable union all select id, name, 'data7' col, data7 as value from yourtable ) src where value > 0
See <a href="http://sqlfiddle.com/#!2/afa54/3" rel="nofollow">SQL Fiddle with Demo</a>