Geek asks and answers

How to read horizontal values ​​in a database?

assuming I have a db that has a 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

information is data fields, and I would like to count all the time when a certain discriminant, such as "greater than 0"

as I thought it was a loop called by all fields and a counter, or COUNT()for each field DATA, so SUM()those 7requests ... who has another idea?

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

+5
source share
2 answers

, , .. . , , , .

, . , .

. @bluefeet, , . :

  • :

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

  • DATA, , :

    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
;

    ( , @bluefeet, UNION.)

+5

, , . MySQL univot, UNION ALL . , . - :

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

SQL Fiddle with Demo

+3

More articles:


All Articles