sql - Sum Based on Date -


i have code want sum every quantity based on year. have written code thought sum charges in 2016 , 2017, isn't running correctly.

i added 2 different types of partition by statements test , see if either work , don't. when take them out, annual column shows me quantity specific receipt.

here current code:

select    receiptnumber   ,quantity   ,date   ,sum(case when (date >= '2016-01-01' , date < '2017-01-01')          quantity          else 0 end)         on (partition date)          annual2016   ,sum(case when (date >= '2017-01-01' , date < '2018-01-01')          quantity          else 0 end)          on (partition receiptnumber)         annual2017 table1 group receiptnumber, quantity, date 

i data

receiptnumber    quantity    date           annual2016    annual2017 1                5           2016-01-05     17            13 2                11          2017-04-03     17            13 3                12          2016-11-11     17            13 4                2           2017-09-09     17            13 

here sample of of data pulling from:

receiptnumber    quantity    date            1                5           2016-01-05 2                11          2017-04-03 3                12          2016-11-11 4                2           2017-09-09 5                96          2015-07-08 6                15          2016-12-12 7                24          2016-04-19 8                31          2017-01-02 9                10          2017-0404 10               18          2015-10-10 11               56          2017-06-02 

try

select  .. sum(case when (date >= '2016-01-01' , date < '2017-01-01')          quantity          else 0 end)         on () annual2016 sum(case when (date >= '2017-01-01' , date < '2018-01-01')          quantity          else 0 end)         on ()as annual2017 ..  date >= '2016-01-01' , date < '2018-01-01' 

Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -