Hi, I'm trying to figure out how to count the number of rows within a fixed range of the current row value. My table looks like this:
SELECT chr_pos FROM mutations_crosstab_9615_99 WHERE bta = 38 LIMIT 10 chr_pos 138 140 163 174 187 187 188 208 210 213 chr_pos is integer and represents the base pair position along a chromosome. It looks to me like a window function would be appropriate but I cannot figure out the correct syntax. What I want to do is count the number of rows within +/- 20 of chr_pos (the current row). Given the above example, for chr_pos = 138 I want the count of rows between 118 and 158. For chr_pos 187 I want the count of rows between 167 and 207 etc for all rows. The result I'm looking for should look like the following: chr_pos,num_variants 138,2 140,2 163,2 174,4 187,4 187,4 188,4 208,6 210,3 213,1 Is there a way to do this with a window function? Any help would be appreciated. Thanks Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
