代码之家 › 专栏 › 技术社区 › Richard Herron

SQLite-基于其他列中的索引对列的小部分求和

aggregate-functions group-by sqlite

0

Richard Herron · 技术社区 · 15 年前

我想总结一下两列的乘积,但我想分几个小节来做。也就是说,每一个 symbol 为每一个 date oi 还有“合同结算”。下面是我的SQLite数据库的一个片段:

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

所以,我想要这个结果:

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

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

但它给出了:

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

谢谢!

1 回复 | 直到 15 年前

1

vickirk 15 年前

我不知道SQLite,但我认为它支持group by子句,例如。

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