Create a group in sql

Question

I have a table with the fields ORDER and FLAG as below and I want to create the GRP column using analytic functions. Can someone adivse how I can do this in sql please?

ORDER	FLAG	GRP
1	Y	1
2	N	1
3	N	1
4	Y	2
5	N	2
6	Y	3
7	Y	4

On what basis the GRP column is calculated? It does not look like it's a function of Order and FLAG. The only way you can write a query to compute GRP is if its value can be derived from the value of Order and FLAG. — Sibgha, Jul 16 '21 at 21:00

score 2 · Answer 1 · answered Jul 16 '21 at 21:08

Looks like you need to start new group at each 'Y'. Then do running count/sum of them:

select
  t.*
  , sum(case flag when 'Y' then 1 end) over(order by order_id asc) as grp
from your_table t

Note: never use columns like order, key, from etc as they are reserved keywords in SQL. That's why I renamed order to order_id.

Create a group in sql

1 Answers1