On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote:
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
Don't think a window function is needed, how about this:
select chr_pos, (
select count(*)
from mutant b
where b.chr_pos between a.chr_pos-20 and a.chr_pos+20
)
from mutant a;
Here's what I get. I dont remember if "between" is inclusive on both
sides or not, but you can change it to suit your needs.
This is the answer I got, which is different than yours, but I think its
right.
chr_pos | count
---------+-------
138 | 2
140 | 2
163 | 2
174 | 4
187 | 3
188 | 4
208 | 5
210 | 4
212 | 4
213 | 4
(10 rows)
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
