CODEWITHSUNDEEP
sqlsql-servert-sql
lilbean
lilbean

Reputation: 35

Group by values that are each multiple of number

This is the table t. I want to group it every time the TotalQty >= 5n (let n = group). i.e. once the TotalQty >= 5n I want to sum together the qty from n-1 to n.

ID  DateCreated  CurrQty
1   01-20-2020      1  
2   01-21-2020      4        
3   01-22-2020      3     
4   01-23-2020      3      
5   01-25-2020      1      
6   02-13-2020      3              
7   02-16-2020      2

With this query I can get pretty close but I doesn't consider the the previous "valid" TotalQty + 5

select DateCreated, CurrQty, TotalQty
, ceiling(TotalQty/5.0) GroupNum
from
(
  select DateCreated, CurrQty
  , SUM(CurrQty) OVER (ORDER BY DateCreated ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) TotalQty
  from t
) t2

ID  DateCreated  CurrQty   TotalQty   GroupNum  
1   01-20-2020      1         1          1                 
2   01-21-2020      4         5          1                  
3   01-22-2020      3         8          2                  
4   01-23-2020      3         11         3
5   01-25-2020      1         12         3 
6   02-13-2020      3         15         3                  
7   02-16-2020      2         17         4

---

How do I get this result?

ID  DateCreated  CurrQty   TotalQty   GroupNum  
1   01-20-2020      1         1          1                 
2   01-21-2020      4         5          1                  
3   01-22-2020      3         8          2                  
4   01-23-2020      3         11         2 (from ID2, 11 >= (5+5))
5   01-25-2020      1         12         3
6   02-13-2020      3         15         3                  
7   02-16-2020      2         17         3 (from ID4, 17 >= (11+5))

And so on, the next group would be until 17+5 = 22

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You need to use a recursive CTE for this:

with cte as (
      select id, datecreated, currqty, currqty as totalqty, 1 as groupnum
      from t
      where id = 1
      union all
      select t.id, t.datecreated, t.currqty,
             (case when cte.totalqty >= 5 then t.currqty else t.currqty + cte.totalqty end),
             (case when cte.totalqty >= 5 then groupnum + 1 else groupnum end)
      from cte join
           t
           on t.id = cte.id + 1
     )
select *
from cte;

EDIT:

Hold on. I think the answer is simpler.

select t.*,
       1 + ceil((totalqty - qty + 1) / 5.0)
from (select t.*,
             sum(qty) over (order by date) as totalqty
      from t
     ) t;

Upvotes: 1

Related Questions