Re: ignore use group by for some column

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 10 Dec 2007 10:55:29 +0100
Message-ID: <475D0D11.3090308@gmail.com>

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 -

>
>
>
> 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 Received on Mon Dec 10 2007 - 03:55:29 CST