1

Every 4 rows need to be summed up and displayed against that group but every group has different number of rows, at the end of the group for the odd rows sum is different.

For example, in the group if just one row is there then it will be value = X + last 3 rows value from the previous group similarly if 2 rows remaining for first row in the group will be X + last 3 rows value from the previous group for the 2nd row it will be from the previous group X + previous row from the group and last 2 rows value from the previous group at the end it should be total 4 rows.

enter image description here

I have tried this query doesn't seem to be working for some reason:

            declare @temptable table(RetailerID int, retailer nvarchar(50),category nvarchar(50),Weekid int,value bigint)
            Insert into @temptable values(34,'R1','a',1,10)
            insert into @temptable values(34,'R1','a',2,10)
            insert into @temptable values(34,'R1','a',3,4)
            insert into @temptable values(34,'R1','a',4,5)
            insert into @temptable values(34,'R1','b',1,2)
            insert into @temptable values(34,'R1','b',2,4)
            insert into @temptable values(34,'R1','b',3,5)
            insert into @temptable values(34,'R1','b',4,3)
            insert into @temptable values(34,'R1','b',5,1)
            insert into @temptable values(34,'R1','c',1,35)
            insert into @temptable values(34,'R1','c',2,68)
            insert into @temptable values(34,'R1','c',3,43)
            insert into @temptable values(34,'R1','c',4,4)
            insert into @temptable values(34,'R1','c',5,5)

            select retailer,category,Weekid,isnull(sum(value),0)    from @temptable td 
            where Weekid  between 1 and 12 and RetailerID=34   
            group by retailer,category,Weekid  order by retailer,category,Weekid;
            with cte as
            ( select retailer,category,Weekid,value,
                    Rangkgrp = (row_number() over (order by category) - 1) / 4
            from @temptable 
            )
            select retailer,category,Weekid,value,
                sumofvalue=sum(value) over (partition by Rangkgrp),Rangkgrp
            from cte 
            order by category,Weekid

Result my query produces which is not what I am looking for. The last row in category b is adding up values from category C ,this is not what I want it has to be from Category b and in category C last two rows are incorrect Category C with Week4 should be 150 and with Week5 should be 120.

enter image description here

How 13 and 120 are calculated: It's Category b Week 2 + 3 + 4 +5 values which is (4+5+3+1) and for category C its again the same thing Week 2 + 3 + 4 +5 values which is (68+43+4+5)=120 and if there was another row in Category b/c it should be Week 3+4+5+6 and so on there can be gap in the weeks but that doesn't matter it should just pick up the next week in the group but under same category.

Let me try to explain again for category a we have 4 rows which is perfectly fine so the value is W1+2+3+4 = sum(10+10+4+5)=29 so against category I am adding one more column and that 29 will be displayed against all the weeks for category a similarly for category b and c I am adding up w1+2+3+4 =sum(2+4+5+3)=14 will be displayed against category b for first 4 rows now I am left with W5 for category b so W5 is calculated as current week(W5) + 3 Previous week) and that value should be displayed against Weeks 5 in category b.

In the same category there are many other weeks ex : first partition has 88 weeks it's just calculating the values for first 4 rows then it starts adding up current - 3 previous weeks it shouldn't work that way ..it should be like 22 groups of 4 rows for each group value should be the sum of 4 rows where as if its 78 rows in the partition then 19 groups of 4 rows + 3 rows where it should perform for each row Current weeks + 3 preceding weeks and here I am not sure each partition might have odd or even number of weeks.

  • You use "even"/"odd" in a very odd way ;) You mean "multiple of 4" / "not multiple of 4" – ypercubeᵀᴹ Feb 8 at 9:16
4

You seem to need rolling sums over windows of 4 rows - so there need to be some correction for the first 3 rows of each group inside every partition:

with cte as
(   select 
        RetailerID, Category, WeekId, value,
        row_number() over
            (partition by RetailerID, Category
             order by WeekId)
            as rn,
        count(*) over
            (partition by RetailerID, Category)
            as tc,  -- total count per partition
        sum(value) over
            (partition by RetailerID, Category
             order by WeekId
             rows between 3 preceding
                      and current row)
            as sv
    from temptable 
    where Weekid between 1 and 12 
      and RetailerID = 34
)
select
    RetailerID, Category, WeekId, value,  
    case when rn <= floor(tc / 4) * 4
        then case (rn % 4)
                 when 1 then lead(sv, 3) over w
                 when 2 then lead(sv, 2) over w
                 when 3 then lead(sv, 1) over w
                 else sv
             end
        else sv
    end as sum_value
from
    cte
window w as 
    (partition by RetailerID, Category order by WeekId) ;

Test at dbfiddle.uk.

Works in MySQL 8 and SQL Server (2012+).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.