CODEWITHSUNDEEP
sqloracle-database
vicky
vicky

Reputation: 415

aggregation sum for separated groups using pivot?

I'm trying to get the aggregation amount over the past rolling 3 days for each separated group. However, I don't get what I expected. Can anyone show me how to produce the expected output, maybe using pivot?

however, my result separated the groups, giving me result when the group is red, and null if it's yellow. however, I want the result such that even though the group is yellow, the data is still not null, but just duplicate the previous number. Maybe try it with pivot? 

select *,
case when group = 'Red' then count(NVL(amount,0)) OVER (PARTITION BY ACCOUNT, group ORDER BY day_number range 2 PRECEDING),
case when group = 'Yellow' then count(NVL(amount,0)) OVER (PARTITION BY ACCOUNT, group ORDER BY day_number range 2 PRECEDING)
from table

expected output 

Account Group   Sum day_number  Red Yellow
abc     Red     20  1           20  0
abc     Red     30  3           50  0
abc     Yellow  40  4           50  40
def     Red     20  5           20  40
def     Yellow  50  8           0   50
def     Yellow  20  9           0   70

wrong output 

Account Group   Sum day_number  Red Yellow
abc     Red     20  1           20  
abc     Red     30  3           50  
abc     Yellow  40  4               40
def     Red     20  5           20  
def     Yellow  50  8               50
def     Yellow  20  9               70

Upvotes: 0

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think you mostly want sum() instead of count():

select t.*,
       (case when group = 'Red'
             then sum(amount) over (partition by account, group order by day_number range between 2 preceding and current row)
        end) as red,
       (case when group = 'Yellow'
             then sum(amount) over (partition by account, group order by day_number range between 2 preceding and current row)
        end) as red
from table t

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

If you want to make red and yellow columns independent from account and group then simply remove partition by part.

dbfiddle demo

select t.*,
       nvl(sum(case grp when 'Red'    then amount end) 
           over (order by day_number range 2 preceding), 0) red,
       nvl(sum(case grp when 'Yellow' then amount end) 
           over (order by day_number range 2 preceding), 0) yellow
  from t

which gives us result:

ACCOUNT GRP        AMOUNT DAY_NUMBER        RED     YELLOW
------- ------ ---------- ---------- ---------- ----------
abc     Red            20          1         20          0
abc     Red            30          3         50          0
abc     Yellow         40          4         30         40
def     Red            20          5         50         40
def     Yellow         50          8          0         50
def     Yellow         20          9          0         70

Note that there is difference in third row in column RED (you typed 50, query shows 30), but for day 4 we have only one red value from two previous days, which is 30.

If you simply need to repeat values for nulls then use lag instead, but this makes no sense for me.

You can do it with pivot, but you had to prepare data properly in subquery, then make pivot. In this case case when syntax is more readable.

Upvotes: 1

Related Questions