sql - Writing subquery within SUM using values of 1 table -

now have table , trying calculate each book_id total sales in past 100 days every day in past 1 year.

book_id location seller daily_sales order_day  abc      1        xyz   100         2017-05-05  abc      1        xyz   120         2017-05-07  abc      1        xyz   40          2017-02-10  .  .  . 

so trying expect in result is:

book_id order_day sum  abc    2017-05-05 100+40  abc    2017-05-07 100+120+40  abc    2017-02-10 40 

for wrote query this:

select book_id, to_char(order_day),      sum(case when order_day between order_day -100 , order_day daily_sales else 0 end) sum     bookdetailstable     location = 1 , order_day between to_date('20170725','yyyymmdd') - 359 , to_date('20170725','yyyymmdd')     group seller, book_id, order_day  

i guess doing wrong , should write select statement within sum statement select data past 100 days.

you should result

select  a.book_id,          a.order_day,         (   select sum(b.daily_sales)              bookdetailstable b              a.book_id = b.book_id              , b.order_day between a.order_day -100 , a.order_day         ) bookdetailstable a.order_day between add_months(trunc(sysdate),-12) , trunc(sysdate) 

if understand principle of query, should able add other restrictions, seller or location