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:
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