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