Wu Fei Wu Fei - 24 days ago 8
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
... ... ... ...


table1:

name min max
(chr1,(10001,20000))
(chr1,(30001,40000))
(chr2,(110001,260000))
(chr2,(160001,360000))
(chr3,(260001,410000))
(chr3,(360001,460000))
(chr3,(410001,560000))
(chr4,(460001,610000))
(chr4,(560001,660000))
(chr4,(610001,710000))


table2:

name value
(chr1,10001)
(chr1,10015)
(chr1,10026)
(chr1,10030)
(chr1,30038)
(chr2,110101)
(chr2,160001)
(chr3,360101)
(chr3,410101)
(chr4,610100)
(chr4,610001)


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]

Answer
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
Comments