CODEWITHSUNDEEP
mysqlsql
parkjbdev
parkjbdev

Reputation: 73

select avg, sum between rows with range

idx some_value
1 298
2 842
3 982
... ...
100 844
start_end_key start_idx end_idx
1 1 2
2 3 40
3 1 100

How can I select avg or sum of example_table between rows of start_idx and end_idx

The table I want is below

start_end_key avg_of_some_values sum_of_some_values counts
1 average of example_table between idx 1 and 2 sum of example_table between idx 1 and 2 2
2 average of example_table between idx 3 and 40 sum of example_table between idx 3 and 40 38
3 average of example_table between idx 1 and 100 sum of example_table between idx 1 and 100 100

I've tried this

select avg(some_value), sum(some_value), count(*)
from example_table where example_table.idx between ??? and ???

but ??? does not allow multiple rows.

Upvotes: 0

Views: 38

Answers (1)

eshirvana
eshirvana

Reputation: 24603

here is how

select start_end_key , avg(some_value), sum(some_value) , count(*)
from example_table et
join range_table rt on et.idx between start_idx and end_idx
group by start_end_key

Upvotes: 2

Related Questions