Search code examples
sqlsql-servert-sqlgroupingaggregation

Tag a row based on aggregate value condition

Here is my dataset, where order is the fix sequence of each product.

Initial data set

What I want is another column in here, lets say TagID. TagID is an int value that will based on the aggregate Count column, group it by product if it is greater or equal to 5.

So the dataset would look like this:

Desired results

How can I accomplish this in SQL Server?

Thanks in advance.

Solution

  • This requires a recursive CTE. You already have a numbering column, so this is pretty simple:

    with cte as (
      select product, order, count, 1 as grp, count as s
      from t
      where order = 1
      union all
      select cte.product. t.order, t.count,
             (case when cte.s + t.count >= 5 then grp + 1 else grp end),
             (case when cte.s + t.count >= 5 then t.count else cte.s + t.count end)
      from cte join
           t
           on t.product = cte.product and t.order = cte.order + 1
     )
select *
from cte;