select - conditional sum in sql, get correct sum from another table based on a condition in a field -

i have table (tbl1) this, in sale amount "store 3" incorrect.

**date      store       sale** mar, 2013   store 1     100 apr, 2013   store 1     80 mar, 2013   store 2     70 mar, 2013   store 3     125 apr, 2013   store 3     80 

the correct amount in table (tbl2):

**date      store       sale** mar, 2013   store 3     140 apr, 2013   store 3     170 

now, need write query generates results below:

**store     total_sale** store 1     180 store 2     70 store 3     310 

i tried different ways of writing case statements, i'm getting wrong total. have simplified real question here hoping community. thank you!

give try,

select  a.store,         coalesce(c.totalsale, b.totalsale) totalsale    (select distinct store table1)         inner join         (             select  store, sum(sale) totalsale                table1             group   store         ) b on a.store = b.store         left join         (             select  store, sum(sale) totalsale                table2             group   store         ) c on a.store = c.store 

• sqlfiddle demo