Count SQL Syntax COUNT (value) multiple columns

Question

Hello im trying to learn about Count in sql, but i cant even count how many times a value is in a specific column

My database structure is this. (table : natural)

ID |  one |  two | three |

1  |   34 |  45  |  80  |
2  |   41 |  34  |  7   |
3  |   7  |  18  |  22  |
4  |   8  |  7   |  45  |

Im trying this

 $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 just 1 column in count i cant get the result..

What i need is count how many times is a "value" (for example 7) in all the columns

like in this example value 7 = 3 total (multiple columns)

How can i make a sql like that.

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 SUCK, thanks for your answers, but i think my problems is with mysql_query () cause i always got a syntax problem, with all your answers and its obviously me.

$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 thats the correct syntax :D

just a note. mysql_query is deprected. learn mysqli or PDO. — itachi
– itachi, Commented May 21, 2013 at 5:07

peterm · Accepted Answer · 2013-05-21 03:54:22Z

7

This one will give you the right answer even if values repeat across 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 following data

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

Output will be

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

answered May 21, 2013 at 3:54

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jules Martinez Over a year ago

thanks!!!! i have a syntax problem when building mysql_query() pls , se my edit. and sorry i know i suck

peterm Over a year ago

@JulesMartinez You're welcome :). NATURAL is a reserved word in MySql. Therefore if you name your table or column with reserved name (which I would not recommend) you always need to use backticks '`' around table or column name respectively.

Jules Martinez Over a year ago

yep , and i tried 'natural'" @ the end of the query but the syntax problem near natural remains. OH SORRY!!! backticks ``thanks!!

Collectives™ on Stack Overflow

Count SQL Syntax COUNT (value) multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related