得到每个不同值在"Multiple"中的计数.列 [英] get the count of each distinct value in "Multiple" columns

问题描述

所以可以说我有

| id | value |
--------------
|  1 |   A   |
|  2 |   B   |
|  3 |   B   |
|  4 |   A   |
|  5 |   A   |

然后运行以下SQL语句

and then run the following SQL statment

SELECT value, COUNT(*) AS `num` FROM test2 GROUP BY value

我会得到A:3和B:2

I would get A: 3 and B: 2

太好了，但是如果我想从多个列中合并怎么办

Great, but what If I wanted to combine from multiple columns

| id | value | Value2 | Value 3|
---------------------------------
|  1 |   A   |   A    |    A   |
|  2 |   B   |   A    |    B   |
|  3 |   B   |   B    |    B   |
|  4 |   A   |   A    |    A   |
|  5 |   A   |   B    |    A   |

在上述A:9 B:6中

.目前，我只能运行上述语句3次，然后在php端添加该值.但是我认为可能只需1条语句就可以完成所有工作.

in the above A:9 B:6. Currently I can just run the above statment 3 times, and then add the value on the php side. But I figured there might be a neat little trick to get it all done in 1 statement.

推荐答案

使用Union all将三列组合为单列，然后在外部查询中查找每个组的计数.试试这个.

Combine the three columns into single column using Union all then find the count of each group in outer query. Try this.

SELECT value,
       Count(value)
FROM   (SELECT value AS value FROM test2
        UNION ALL
        SELECT value2 FROM test2
        UNION ALL
        SELECT value3 FROM test2)a
GROUP  BY value 

这篇关于得到每个不同值在"Multiple"中的计数.列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！