Hello im trying to learn about Count in sql, but i cant even count how many times a value is in a specific column
你好,我正在尝试学习sql中的Count,但我甚至无法计算一个值在特定列中的次数
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
我需要计算的是在所有列中有多少次“值”(例如7)!
like in this example value 7 = 3 total (multiple columns)
如本例中的值7 = 3 total(多列)
How can i make a sql like that.
我怎么能做出这样的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 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.
谢谢你的回答,但我认为我的问题是mysql_query(),因为我总是有语法问题,所有的答案都是我的。
$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
要修改最后的代码,只需使用1和2……这就是正确的语法:D。
1 个解决方案
#1
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 |
#1
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 |