sql - aggregating and analytical functions -

sql - aggregating and analytical functions -

how can difference of nsr , dsr each month , % increment /decrease next sql result:

type period typecount typetotal ---------------------------------------------- dsr 2014-10 88 117 nsr 2014-10 29 117 dsr 2014-09 139 363 nsr 2014-09 224 363 dsr 2014-08 226 439 nsr 2014-08 213 439 dsr 2014-07 181 409 nsr 2014-07 228 409 dsr 2014-06 103 321 nsr 2014-06 218 321 dsr 2014-05 334 552 nsr 2014-05 218 552 dsr 2014-04 188 398 nsr 2014-04 210 398 dsr 2014-03 199 447 nsr 2014-03 248 447 dsr 2014-02 166 505 nsr 2014-02 339 505 dsr 2014-01 294 559 nsr 2014-01 265 559

i suggest using conditional aggregation on 1 row , doing work in structure:

select period, max(case when type = 'dsr' typecount end) typecount_dsr, max(case when type = 'nsr' typecount end) typecount_nsr, max(typetotal) typetotal table t grouping period;

then can calculations pretty easily, like:

with p ( select period, max(case when type = 'dsr' typecount end) typecount_dsr, max(case when type = 'nsr' typecount end) typecount_nsr, max(typetotal) typetotal table t grouping period ) select p.*, (typecount_dsr - typecount_nsr) diff, (typecount_dsr - typecount_nsr) / lag(typecount_dsr - typecount_nsr) on (order period) diffinc p;

sql oracle window-functions