CODEWITHSUNDEEP
sqlpostgresqlgroup-bypivotunpivot
Breno1982
Breno1982

Reputation: 45

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

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:

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.

Upvotes: 2

Views: 41

Answers (1)

GMB
GMB

Reputation: 222492

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

Upvotes: 1

Related Questions