How to count how many times a specific value appeared on each columns and group by range

Question

I'm new on postgres and I have a question:

I have a table with 100 columns. I need to count the values from each columns and count how many times they appeared, so I can group then based on the range that they fit

I have a table like this(100 columns)

+------+------+------+------+------+---------+--------+
| Name | PRB0 | PRB1 | PRB2 | PRB3 | ....... | PRB100 |
+------+------+------+------+------+---------+--------+
| A    |   15 |   6  |   47 |   54 |   ..... |      8 |
| B    |   25 |   22 |   84 |   86 |   ..... |     76 |
| C    |   57 |   57 |   96 |   38 |   ..... |     28 |
+------+------+------+------+------+---------+--------+

And need the output to be something like this

+------+---------------+----------------+----------------+----------------+-----+-----------------+--+
| Name | Count 0 to 20 | Count 21 to 40 | Count 41 to 60 | Count 61 to 70 | ... | Count 81 to 100 |  |
+------+---------------+----------------+----------------+----------------+-----+-----------------+--+
| A    |             5 |             46 |             87 |             34 | ... |              98 |  |
| B    |             5 |              2 |             34 |             56 | ... |              36 |  |
| C    |             7 |             17 |             56 |             78 | ... |              88 |  |
+------+---------------+----------------+----------------+----------------+-----+-----------------+--+

For Name A we have:

5 times the number between 0 and 20 apeared
46 times the number between 21 and 40 appeared
86 times the number between 41 and 60 appeared

Basicaly I need something like the function COUNTIFS that we have on Excel. On excel we just need to especify the range of columns and the condition.

GMB · Accepted Answer · 2020-05-19T22:53:01.750

You could unpivot with a lateral join, then aggregate:

select
    name,
    count(*) filter(where prb between 0  and 20) cnt_00_20,
    count(*) filter(where prb between 21 and 50) cnt_21_20,
    ...,
    count(*) filter(where prb between 81 and 100) cnt_81_100
from mytable t
cross join lateral (values(t.prb0), (t.prb1), ..., (t.prb100)) p(prb)
group by name

Note, however, that this still requires you to enumerate all the columns in the values() table constructor. If you want something fully dynamic, you can use json instead. The idea is to turn each record to a json object using to_jsonb(), then to rows with jsonb_each(); you can then do conditional aggregation.

select 
    name,
    count(*) filter(where prb::int between 0  and 20) cnt_00_20,
    count(*) filter(where prb::int between 21 and 50) cnt_21_20,
    ...,
    count(*) filter(where prb::int between 81 and 100) cnt_81_100
from mytable t
cross join lateral to_jsonb(t) j(js)
cross join lateral jsonb_each( j.js - 'name') r(col, prb)
group by name

Thanks a lot, it worked perfectly. I did the first method by enumerate all the 100 columns. I will study this method line by line to understand each command line. — Breno1982, May 19 '20 at 23:31

How to count how many times a specific value appeared on each columns and group by range

1 Answers1