Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ignore use group by for some column
On Dec 8, 7:22 pm, Maxim Demenko <mdemenko_at_gmail.com> wrote:
> nova1427_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 Received on Mon Dec 10 2007 - 01:35:41 CST