Postgresql pass multiple ranges and check if value in range

Question

I have a user table that has age column. I need to be able to dynamically create set of ranges and pass it to the procedure, for example:

[
  [0, 10],
  [10, 18],
  [18, 35],
]

As a result, it needs to return values count that belong to each range. Example:

|min|max|count|
---------------
|  0| 10|   24|
| 10| 18|  111|
| 18| 35|    0|

How can I implement such procedure?

and check if the value within this range - which "value" should be checked? Please edit your question (by clicking on the edit link below it) and add some sample data and the expected output based on that data as formatted text. See here for some tips on how to create nice looking text tables. (edit your question - do not post code or additional information in comments) — user330315
– user330315, Commented Aug 4, 2020 at 11:01

user330315 · Accepted Answer · 2020-08-04 11:12:28Z

3

You can create a list of ranges and use that to join to the table then group by the matched range:

with ranges (range) as (
  values 
    ( int4range(10,30) ), 
    ( int4range(10,18) ), 
    ( int4range(18,35) )
)
select r.range, count(t.age)
from ranges r
  left join the_table t on t.age <@ r.range
group by r.range;

answered Aug 4, 2020 at 11:12

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql pass multiple ranges and check if value in range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related