CODEWITHSUNDEEP
sqlpostgresql
Doe
Doe

Reputation: 379

Get count of numbers from all columns in a large table

I have the following table 

ID    A1     A2     A3     A4     A5      A6
1    324    243    3432   23423  342     342
2    342    242    4345   23423  324     342

How do I write a query that will give me the no.of times a number is appearing in any of the above columns. For example, this is the output I am looking for - 

324     2
243     1
3432    1
23423   1
342     3
242     1
4345    1
23423   1

Upvotes: 0

Views: 54

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

In Postgres, you can use lateral joins to unpivot values. I find this more direct than using an array or union all:

select v.a, count(*)
from t cross join lateral
     (values (a1), (a2), (a3), (a4), (a5), (a6)
     ) v(a)
group by v.a;

Here is a db<>fiddle.

Upvotes: 0

Robert Nubel
Robert Nubel

Reputation: 7532

There are a number of ways to do this, but my first thought is to use unnest:

rnubel=# CREATE TABLE mv (a int, b int, c int);
CREATE TABLE
rnubel=# INSERT INTO mv (a, b, c) VALUES (1, 1, 1), (2, 2, 2), (3, 4, 5);
INSERT 0 3
rnubel=# SELECT unnest(array[a, b, c]) as value, COUNT(*) from mv GROUP BY 1;
 value | count
-------+-------
     5 |     1
     4 |     1
     2 |     3
     1 |     3
     3 |     1
(5 rows)

unnest is a handy function that turns an array into a set of rows, so it expands the array of column values into one row per column value. Then you just group and count as usual.

Upvotes: 1

kjmerf
kjmerf

Reputation: 4345

Brute force method:

SELECT Value
    ,COUNT(1) AS ValueCount
FROM (
    SELECT A1 AS Value
    FROM t

    UNION ALL

    SELECT A2
    FROM t

    UNION ALL

    SELECT A3
    FROM t

    UNION ALL

    SELECT A4
    FROM t

    UNION ALL

    SELECT A5
    FROM t

    UNION ALL

    SELECT A6
    FROM t
    ) x
GROUP BY Value

Upvotes: 0

Related Questions