SQLite -- sum over small sections of column based on index in other column

Question

I would like to sum over the product of two columns, but I would like to do it in small sections. That is, for every symbol for every date I would like to sum the product of oi and 'contract_settle`. Here's a snippet of my SQLite db:

date    symbol  cont    oi  contract_settle
20030103    NVLS1C      100 NA
20030103    NVLS1C  03F 19  3125
20030103    NVLS1C  03G 8   3128
20030103    NVLS1C  03H 14  3130
20030103    NVLS1C  03M 59  3140
20030103    BGEN1C      90  NA
20030103    BGEN1C  03F 20  4149
20030103    BGEN1C  03G 30  4154
20030103    BGEN1C  03H 20  4158
20030103    BGEN1C  03M 20  4174

So, I would like this result:

date    symbol  cont    oi  contract_settle oi_dollar
20030103    NVLS1C      100 NA          313479
20030103    NVLS1C  03F 19  3125    
20030103    NVLS1C  03G 8   3128    
20030103    NVLS1C  03H 14  3130    
20030103    NVLS1C  03M 59  3140    
20030103    BGEN1C      90  NA          374240 
20030103    BGEN1C  03F 20  4149    
20030103    BGEN1C  03G 30  4154    
20030103    BGEN1C  03H 20  4158    
20030103    BGEN1C  03M 20  4174    

I thought this would work:

SELECT *,
(SELECT sum(oi * contract_settle) WHERE symbol=symbol) as oi_dollar
FROM ann

But it gives this:

date    symbol  cont    oi  contract_settle oi_dollar
20030103    NVLS1C      100 NA          NA
20030103    NVLS1C  03F 19  3125            59375
20030103    NVLS1C  03G 8   3128            25024
20030103    NVLS1C  03H 14  3130            43820
20030103    NVLS1C  03M 59  3140            185260
20030103    BGEN1C      90  NA          NA
20030103    BGEN1C  03F 20  4149            82980
20030103    BGEN1C  03G 30  4154            124620
20030103    BGEN1C  03H 20  4158            83160
20030103    BGEN1C  03M 20  4174            83480

Thanks!

Answer 1

I don't know SQLite but I assume it supports group by clauses, e.g.

SELECT date, symbol, SUM(oi *  contract_settle) AS oi_dollar
  FROM add
 GROUP BY date, symbol;