Read SQL syntax COUNT (value) of multiple columns

Question

Hi, I am trying to find out about Count in sql, but I can’t even count how many times the value is in a specific column

My database structure is as follows. (table: natural)

ID | one | two | three | 1 | 34 | 45 | 80 | 2 | 41 | 34 | 7 | 3 | 7 | 18 | 22 | 4 | 8 | 7 | 45 | 

I'm trying to do it

  $result=mysql_query("SELECT COUNT(one) AS total FROM natural WHERE one=7")or die("Error: ".mysql_error()); $data=mysql_fetch_assoc($result); echo $data['total']; 

but even with one column in the account, I can’t get the result.

I need to count how many times this “value” (for example, 7) in all columns

as in this example, the value 7 = 3 in total (several columns)

How can I do such sql.

EDIT: Trying this (where is my syntax problem?)

 $result=mysql_query("SELECT COUNT(DISTINCT id) TotalCount FROM tblname WHERE 7 IN (one, two, three)")or die("Error: ".mysql_error()); $data=mysql_fetch_assoc($result); echo $data['TotalCount']; 

I ACCEPT, thanks for your answers, but I think my problems are related to mysql_query (), because I always got a syntax problem with all of your answers and, obviously, with it.

 $result=mysql_query("SELECT (SUM(CASE WHEN one = 7 THEN 1 ELSE 0 END) + SUM(CASE WHEN two = 7 THEN 1 ELSE 0 END) + SUM(CASE WHEN three = 7 THEN 1 ELSE 0 END)) TotalCount FROM natural")or die("Error: ".mysql_error()); $data=mysql_fetch_assoc($result); echo $data['TotalCount']; 

To fix this last code, just use one and two ... so on, and natural is the correct syntax: D

Answer 1

This will give you the correct answer, even if the values ​​are repeated in columns

 SELECT (SUM(CASE WHEN one = 7 THEN 1 ELSE 0 END) + SUM(CASE WHEN two = 7 THEN 1 ELSE 0 END) + SUM(CASE WHEN three = 7 THEN 1 ELSE 0 END)) TotalCount FROM table1 

SQLFiddle

If you have the following data

 | ID | ONE | TWO | THREE | -------------------------- | 1 | 34 | 45 | 80 | | 2 | 41 | 7 | 7 | | 3 | 7 | 18 | 22 | | 4 | 7 | 7 | 45 | 

The output will be

 | TOTALCOUNT | -------------- | 5 |