Clever Geek Handbook

Get count of each distinct value in Multiple columns

so let's say that I have

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

and then run the following SQL file

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

I would get A: 3 and B: 2

Great, but what if I would like 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   |

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

+2


source to share


2 answers


Combine the three columns into one column using Union all

then find the count of each group in the outer query. Try it.



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
+1


source


First you can COUNT

values ​​for each individual column and group, then combine the results with UNION ALL

and finally SUM

for each group.



SELECT `num`, SUM(`num`)
FROM (
    SELECT value, COUNT(*) AS `num` FROM test2 GROUP BY value
    UNION ALL
    SELECT value2, COUNT(*) AS `num` FROM test2 GROUP BY value2
    UNION ALL
    SELECT value3, COUNT(*) AS `num` FROM test2 GROUP BY value3
) GROUP BY `num`
+1


source






More articles:


All Articles