mysql - SQL Group By with Sum for each date and using max date -

i've seen lot of similar questions nothing quite nails particular problem.

i have table storing multiple positions each account. changes stored deltas. take example on day 1 following...

ac_id | pos_id | asat       | val     1 |      1 | 2016-01-01 | 100     1 |      2 | 2016-01-01 | 200 

the total value ac_id 1 300 on 01/01/2016.the next day may update be...

ac_id | pos_id | asat       | val     1 |      1 | 2016-01-01 | 100     1 |      2 | 2016-01-01 | 200     1 |      2 | 2016-01-02 | 250 

now total value ac_id 1 350. because new record pos_id 2 overrides previous, value pos_id 1 has not changed. in order remove pos_id 1 table change like...

ac_id | pos_id | asat       | val     1 |      1 | 2016-01-01 | 100     1 |      2 | 2016-01-01 | 200     1 |      2 | 2016-01-02 | 250     1 |      1 | 2016-01-03 | 0 

now value changes 250 on day 3.

i can calculate value @ given date subquery so

select sum(val) position p1 p1.asat =    (select max(p2.asat) position p2    p1.ac_id  = p2.ac_id    ,   p1.pos_id = p2.pos_id    ,   p2.date <= [chosen date]) 

what i'd write single query give me total value every ac_id every asat. if not delta storage mechanism achieve using

select ac_id, asat, sum(val) position group ac_id, asat order asat desc 

what i'm looking achieve above take account join on table. if use above i'll totals changed on asat date , not of existing values haven't changed.

in above example should equate resultset of

ac_id | asat       | sum(val)     1 | 2016-01-01 |      300     1 | 2016-01-02 |      350     1 | 2016-01-03 |      250 

here's example of data vs output

ac_id | pos_id | asat       | val     1 |      1 | 2016-01-01 | 100     1 |      2 | 2016-01-01 | 200     1 |      2 | 2016-01-02 | 250     1 |      1 | 2016-01-03 | 0     2 |      1 | 2016-01-02 | 500     3 |      7 | 2016-01-02 | 1000     3 |      7 | 2016-01-03 | 1000     3 |     12 | 2016-01-03 | 5000     2 |      1 | 2016-01-04 | 750 

result

ac_id | asat       | sum(val)     1 | 2016-01-01 |      300     1 | 2016-01-02 |      350     1 | 2016-01-03 |      250     2 | 2016-01-02 |      500     2 | 2016-01-04 |      750     3 | 2016-01-02 |     1000     3 | 2016-01-03 |     6000 

i changed how works

although answers below worked performance of them shockingly bad (through no fault of authors!) in order acceptable (i need sub-second return) refactored table include end_date column. column gets updated on each insert set life span of row. if row doesn't have superseding entry end date set 9999-12-31. example above becomes...

ac_id | pos_id | asat       | end_date   | val     1 |      1 | 2016-01-01 | 2016-01-03 |  100     1 |      2 | 2016-01-01 | 2016-01-02 |  200     1 |      2 | 2016-01-02 | 9999-12-31 |  250     1 |      1 | 2016-01-03 | 9999-12-31 |    0     2 |      1 | 2016-01-02 | 2016-01-04 |  500     3 |      7 | 2016-01-02 | 2016-01-03 | 1000     3 |      7 | 2016-01-03 | 9999-12-31 | 1000     3 |     12 | 2016-01-03 | 9999-12-31 | 5000     2 |      1 | 2016-01-04 | 9999-12-31 |  750 

i can remove second join accepted answer , add clause inner join.

select   p1.ac_id,    p1.asat,    sum(p2.val) totalvalue    (select distinct ac_id, asat position) p1 inner join position p2 on   p2.ac_id    =  p1.ac_id ,   p2.asat     <= p1.asat ,   p2.end_date >  p1.end_date group    p1.ac_id,   p1.asat; 

this should give need:

select     p1.ac_id,     p1.asat,     sum(p2.val) total_value     (select distinct p.ac_id, p.asat dbo.position p) p1 inner join dbo.position p2 on     p2.ac_id = p1.ac_id ,     p2.asat <= p1.asat left outer join dbo.position p3 on     p3.ac_id = p1.ac_id ,     p3.pos_id = p2.pos_id ,     p3.asat > p2.asat ,     p3.asat <= p1.asat     p3.ac_id null group     p1.ac_id,     p1.asat 

the query gets of ac_id/asat combinations, grabs rows might fall need totaled, , uses left outer join , check null eliminate rows aren't latest particular pos_id.