2

so lets say I have

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

and then run the following SQL statment

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

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   |

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.

Rapyzak
  • 23
  • 3

2 Answers2

1

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
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

At first you can COUNT values for each separate column and group, then combine results with UNION ALL and at last SUM them 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`
potashin
  • 44,205
  • 11
  • 83
  • 107