Wu Fei Wu Fei - 8 months ago 29
SQL Question

what sql can be used to count value in range

i have 2 table, i want to get the result of

name min max count-of-occource-table2-value
chr1 10001 20000 4
chr1 30001 40000 1
chr2 110001 260000 2
... ... ... ...


name min max


name value

the count plus 1 if table1.name= table2.name && table2.value in table1[min,man]

so how to

select table1.name, table1.min,table1.max, count of the occource where table1.name= table2.name and table2.value in table1[min,man]

SELECT A.name, A.min, A.max, SUM(CASE WHEN A.name = B.name AND (B.value BETWEEN A.min AND A.max) THEN 1 ELSE 0 END) AS countofoccourcetable2value
FROM table1 A
INNER JOIN table2 B ON A.name = B.name
GROUP BY A.name, A.min, A.max