Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ignore use group by for some column
nova1427_at_gmail.com schrieb:
> 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 -
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
18 Col1, 19 lead(col2) over(partition by col1,col2,col3 order by null) ld, 20 col2, 21 Col3, 22 Col4
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 Received on Mon Dec 10 2007 - 03:55:29 CST