David David - 6 months ago 20
MySQL Question

Count item across multiple columns in same table

I have a table that looks like below

TableA

------------------------------------
id| ColumnA | ColumnB | ColumnC
------------------------------------
1 | A | B | B
2 | B | D | D
3 | C | E | A
------------------------------------


How to count the each of occurrence of each item in each column?

The result that I want is like below:

--------------------
Result | Counter
--------------------
A | 2
B | 3
C | 1
D | 2
E | 1


For single column I can use this SQL:

SELECT ColumnA, COUNT(*)
FROM TableA
GROUP BY ColumnA
HAVING COUNT(*) > 0


What about multiple column? Any help would be great!

Answer

Try this;)

select c as Result, count(1) as Counter from (
    select ColumnA c from TableA
    union all
    select ColumnB c from TableA
    union all
    select ColumnC c from TableA ) t
group by c
Comments