I have a table with some key values occurring in one or more columns. Each value can occur zero or more times in each row. How do I efficiently calculate number of occurrences of each value in the entire table?
example:
x
c1 | c2 | c3
----+-----+-----
A . | B . | C
A . | C . | D
E . | C . | B
D . | D . | A
C . | B . | B
desired result:
A 3
B 4
C 4
D 3
E 1
for reference, table has 6 columns, 50K rows, and 2000 distinct values scattered across.
Advertisement
Answer
One idea is to select all the values into a union, then count:
something like:
SELECT data, count(*) FROM (
SELECT col1 as data FROM table
UNION ALL
SELECT col2 as data FROM table
) GROUP BY data