Re: ignore use group by for some column

On Dec 10, 12:55 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> nova1..._at_gmail.com schrieb:
>
>
>
>
>
> > On Dec 8, 7:22 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> >> nova1..._at_gmail.com schrieb:
>
> >>> this is first table
> >>> Col1 Col2 Col3
> >>> A 35 45
> >>> C 24 11
> >>> B 55 32
> >>> A 62 64
> >>> K 51 44
> >>> C 32 22
> >>> A 77 32
> >>> I want make this table like following:
> >>> Col1 Col2 Col3
> >>> A 174 45
> >>> A 174 64
> >>> A 174 32
> >>> C 56 11
> >>> C 56 22
> >>> B 55 32
> >>> K 51 44
> >>> you see Col2 is sum deppendent on Col1 and ignore Col3
> >>> I can make like this
> >>> Col1 Col2
> >>> A 174
> >>> C 56
> >>> B 55
> >>> K 51
> >>> by this query
> >>> Select Col1, sum(Col2) from table group by Col1
> >>> put I need Col3 as it is
> >>> Please help
> >> SQL> with t as (
> >> 2 select 'A' Col1,35 Col2,45 Col3 from dual union all
> >> 3 select 'C',24,11 from dual union all
> >> 4 select 'B',55,32 from dual union all
> >> 5 select 'A',62,64 from dual union all
> >> 6 select 'K',51,44 from dual union all
> >> 7 select 'C',32,22 from dual union all
> >> 8 select 'A',77,32 from dual
> >> 9 )
> >> 10 -- End test data
> >> 11 select Col1,sum(Col2) over(partition by col1) col2,Col3
> >> 12 from t
> >> 13 order by col2 desc
> >> 14 /
>
> >> COL1 COL2 COL3
> >> ---- ---------- ----------
> >> A 174 45
> >> A 174 64
> >> A 174 32
> >> C 56 11
> >> C 56 22
> >> B 55 32
> >> K 51 44
>
> >> 7 rows selected.
>
> >> Best regards
>
> >> Maxim- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thank you so much
> > It is to useful
>
> > But I have another problem
>
> > For example
>
> > This table in Col2, there is repeating dependent on Col3
> > Like Col1=A 66 is repeat two times and Col3 is defined the repeating
>
> > Col1 Col2 Col3 Col4
> > A 35 z 1
> > A 66 y 2
> > A 66 y 3
> > C 33 x 4
> > C 33 x 5
> > C 33 p 6
> > D 12 w 7
> > D 22 v 8
>
> > it should be the result like this
>
> > Col1 Col2 Col3 Col4
> > A 101 z 1
> > A 101 y 2
> > A 101 y 3
> > C 66 x 4
> > C 66 x 5
> > C 66 p 6
> > D 34 w 7
> > D 34 v 8
>
> > I try to use your query, the result is
> > Col1 Col2 Col3 Col4
> > A 167 z 1
> > A 167 y 2
> > A 167 y 3
> > C 99 x 4
> > C 99 x 5
> > C 99 p 6
> > D 34 w 7
> > D 34 v 8
>
> > the result is wrong because the value in Col2 have repeating
>
> > Please help
>
> There are some possibilities to do that, one could be
> SQL> with t as (
> 2 select 'A' Col1,35 Col2,'z' Col3,1 Col4 from dual union all
> 3 select 'A',66,'y',2 from dual union all
> 4 select 'A',66,'y',3 from dual union all
> 5 select 'C',33,'x',4 from dual union all
> 6 select 'C',33,'x',5 from dual union all
> 7 select 'C',33,'p',6 from dual union all
> 8 select 'D',12,'w',7 from dual union all
> 9 select 'D',22,'v',8 from dual
> 10 )
> 11 -- End test data
> 12 select
> 13 col1,sum(nullif(col2,ld)) over(partition by col1) col2,
> 14 col3,
> 15 col4
> 16 from (
> 17 select
> 18 Col1,
> 19 lead(col2) over(partition by col1,col2,col3 order by null) ld,
> 20 col2,
> 21 Col3,
> 22 Col4
> 23 from t)
> 24 order by col4
> 25 /
>
> C COL2 C COL4
> - ---------- - ----------
> A 101 z 1
> A 101 y 2
> A 101 y 3
> C 66 x 4
> C 66 x 5
> C 66 p 6
> D 34 w 7
> D 34 v 8
>
> 8 rows selected.
>
> Best regards
>
> Maxim- Hide quoted text -
>
> - Show quoted text -

This is amazing
But I'm not understand, what happen, what is the presses to know the repeating

Can you explain to me the process?

I want understand to use it for any required.

thank you so much Received on Mon Dec 10 2007 - 10:37:00 CST