how to count horizontal values on a database?

Question

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 COUNT() each DATA field, so SUM() those 7 queries... anyone has another idea?

in this case the result, so "count every DATA field with a value over 0" would be = 7

Do you need to do this dynamically or are the columns fixed? — Brendan Bullen, Feb 26 '13 at 09:54
well, lets assume the column are fixed, so the coulmn are from DATA1 to DATA7 indeed a dynamic solution should be nice. — Matteo Bononi 'peorthyr', Feb 26 '13 at 09:58

score 5 · Accepted Answer · edited May 23 '17 at 12:12

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 finite column set.

There's more than one way to solve this problem. The method suggested by @bluefeet is probably one of the clearer as well as less efficient ones. You could try at least two alternatives:

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
;

(The OR NULL trick is explained here.)

Unpivot the 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.)

very elegant and well explained, thanks a lot, the OR NULL trick is really nice! — Matteo Bononi 'peorthyr', Feb 27 '13 at 11:54

score 3 · Answer 2 · answered Feb 26 '13 at 13:57

Since your data is not normalized you should unpivot 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 SQL Fiddle with Demo

@VAV `UNION ALL` performs better than `UNION` since it does not remove any duplicate records. — Taryn, Feb 26 '13 at 14:06
yes, and do not make any "sorting" but my question about 7-times `union` and aggregation of resulting set of data for large tables - is it production solution? — VAV, Feb 26 '13 at 14:15

how to count horizontal values on a database?

2 Answers2

Linked