PostgreSQL group by greater than equal to-CodePudding

I am trying to find the count over a table (R1) based on whether or not it's value that is greater than or equal to values in another table (R2), grouping based on the values in R2. This would end up having a sum of counts greater than the total number of entries in the table, since some entries could be greater than or equal to multiple values.

For example,

a	b
a	1
b	2
a	3
c	2
a	4
f	1
c	3
g	4

val
1
2
3

The ideal result would look like

val	count_gte
1	8
2	6
3	4

Since 1 has 8 values that appear that are greater than or equal, 2 has 6 values that appear that are greater than or equal, and 3 has 4 values that are greater than or equal.

Thanks for any help you can offer

CodePudding user response：

You can join two tables with r2 <= r1 (greater than or equal)

Demo

select
  r2.val,
  count(*)
from
  r2,
  r1
where
  r2.val <= r1.b
group by
  r2.val
order by
  r2.val

CodePudding user response：

select r2.val, (select count(*) from r1 where r1.b>=r2.val) as count_gte from r2