--order by col3
col1 col2 col3
1 5 1
1 10 2
2 2 3
2 5 4
1 15 5
I hav to get o/p as
col1 sum_col2
1 15
2 7
1 15
I have to group by preserving the order
Pls give me suggestions..
jeneesh
col1 col2 col3
1 5 1
1 10 2--summed here, coz col1 changes
2 2 3
2 5 4--summed here, coz col1 changes
1 15 5--summed here, coz col1 changes
3 25 6--summed here
Message was edited by:
jeneesh
I have to group by preserving the order
with t as
( select 1 as col1, 5 as col2, 1 as col3 from dual union all
select 1 as col1, 10 as col2, 2 as col3 from dual union all
select 2 as col1, 2 as col2, 3 as col3 from dual union all
select 2 as col1, 5 as col2, 4 as col3 from dual union all
select 1 as col1, 15 as col2, 5 as col3 from dual
)
select col1, sum(col2) as col2
from
( select col1, col2, sum(start_of_group) over (order by col3) as group_no
from
( select col1, col2, col3,
decode(lag(col1) over (order by col3), col1, 0, 1) as start_of_group
from t
)
)
group by group_no, col1
;
COL1 COL2
------------- -------------
1 15
2 7
1 15
3 rows selected.
SQL> with t
2 as
3 (
4 select 1 col1,5 col2,1 col3 from dual
5 union all
6 select 1,10,2 from dual
7 union all
8 select 2,2,3 from dual
9 union all
10 select 2,5,4 from dual
11 union all
12 select 1,15,5 from dual
13 union all
14 select 3,25,6 from dual
15 union all
16 select 3,10,7 from dual
17 union all
18 select 3,5,8 from dual
19 union all
20 select 3,15,9 from dual
21 union all
22 select 4,5,10 from dual
23 )
24 select col1,case when s=1 and rownum = 1 then sm
25 when s=1 and rownum <> 1 then sm-lag(sm,1) over(partition by 1 order by col3)
26 when s is null then col2
27 end col2
28 from
29 (
30 select col1,col2,col3,s,sm
31 from
32 (select col1,col2,col3,
33 case when col1=lag(col1,1) over(partition by 1 order by col3) and col1<>lead(col1,1) over(partition by 1 order by col3)
34 then 1
35 when col1=lead(col1,1) over(partition by 1 order by col3)
36 then 0
37 else null end s,
38 sum(col2) over(order by col3) sm
39 from t
40 )
41 where s=1
42 or s is null
43 order by col3
44 )
45 /
COL1 COL2
---------- ----------
1 15
2 7
1 15
3 55
4 5
SQL> with WorkView as(
2 select 1 ID,5 Val,1 SortOrder from dual
3 union all select 1,10,2 from dual
4 union all select 2,2,3 from dual
5 union all select 2,5,4 from dual
6 union all select 1,15,5 from dual
7 union all select 3,25,6 from dual
8 union all select 3,10,7 from dual
9 union all select 3,5,8 from dual
10 union all select 3,15,9 from dual
11 union all select 4,5,10 from dual)
12 select ID,sum(Val) as Val
13 from (select ID,Val,Last_Value(LagSortOrder ignore nulls) over(order by SortOrder) as LagSortOrder
14 from (select ID,Val,SortOrder,
15 case when ID != Lag(ID) over(order by SortOrder)
16 then SortOrder end as LagSortOrder
17 from WorkView))
18 group by ID,LagSortOrder
19 order by LagSortOrder nulls first;
ID VAL
-- ---
1 15
2 7
1 15
3 55
4 5
with ValTable as(
select 1 ID,5 Val,1 SortOrder from dual
union all select 1,10,2 from dual
union all select 2,2,3 from dual
union all select 2,5,4 from dual
union all select 1,15,5 from dual
union all select 3,25,6 from dual
union all select 3,10,7 from dual
union all select 3,5,8 from dual
union all select 3,15,9 from dual
union all select 4,5,10 from dual)
select ID,sum(Val) as Val
from (select ID,Val,SortOrder,
Row_Number() over(order by SortOrder)
-Row_Number() over(partition by ID order by SortOrder) as makeGroup
from ValTable)
group by ID,makeGroup
order by min(SortOrder);
ID VALIn above solution,
-- ---
1 15
2 7
1 15
3 55
4 5
Elic wrote:I've learned a lot, Your answer is very helpful.
<DIV class=jive-quote>I have to group by preserving the order</DIV><PRE>with t as
( select 1 as col1, 5 as col2, 1 as col3 from dual union all
select 1 as col1, 10 as col2, 2 as col3 from dual union all
select 2 as col1, 2 as col2, 3 as col3 from dual union all
select 2 as col1, 5 as col2, 4 as col3 from dual union all
select 1 as col1, 15 as col2, 5 as col3 from dual
)
select col1, sum(col2) as col2
from
( select col1, col2, sum(start_of_group) over (order by col3) as group_no
from
( select col1, col2, col3,
<font face="tahoma,verdana,sans-serif" size="1" color="#000">decode</font>(lag(col1) over (order by col3), col1, 0, 1) as start_of_group
from t
)
)
group by group_no, col1
;
COL1 COL2
<HR>-------------
1 15
2 7
1 15
3 rows selected.
</PRE>