Re: ignore use group by for some column

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 08 Dec 2007 17:22:33 +0100
Message-ID: <475AC4C9.3010307@gmail.com>

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 Received on Sat Dec 08 2007 - 10:22:33 CST