CODEWITHSUNDEEP
sqloracle-database
user3034073
user3034073

Reputation: 25

Is there a way to group this data?

Data Looks like - 

1
2
3
1
2
2
2
3
1
5
4
1
2

So whenever there is a 1, it marks the beginning of a group which includes all the elements until it hits the next 1. So here, 

1 2 3 - group 1
1 2 2 2 3 - group 2

and so on.. What would be the SQL query to show the average for every such group.

I could not figure out how to group them without using for loops or PLSQL code.

Result should look like two columns, one with the actual data and col 2 with the average value-

1 - avg value of 1,2 3
2
3
1 - avg value of 1,2,2,2,3
2
2
2
3
1 - avg value of 1,5,4
5
4
1 - avg value of 1,2
2

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

SQL tables represent unordered sets. There is no ordering, unless a column specifies the ordering. Let me assume that you have such a column.

You can identify the groups using a cumulative sum:

select t.*,
       sum(case when t.col = 1 then 1 else 0 end) over (order by ?) as grp
from t;

? is the column that specifies the ordering.

You can then calculate the average using aggregation:

select grp, avg(col)
from (select t.*,
             sum(case when t.col = 1 then 1 else 0 end) over (order by ?) as grp
      from t
     ) t
group by grp;

Upvotes: 3

Related Questions