Skip to content
Advertisement

Best way to count occurrences in a table?

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
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement