nova1427_at_gmail.com schrieb:
> On Dec 10, 12:55 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>> nova1..._at_gmail.com schrieb:
>>
>>
>>
>>
>>
>>> On Dec 8, 7:22 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>>>> nova1..._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- Hide quoted text -
>>
>> - Show quoted text -
>
>
>
> This is amazing
> But I'm not understand, what happen, what is the presses to know the
> repeating
>
>
> Can you explain to me the process?
>
> I want understand to use it for any required.
>
>
> thank you so much
Well, to begin with, one can look, what yields the subselect. I've
modified a little bit your dataset to make the point:
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,'x',6 from dual union all
8 select 'C',33,'x',7 from dual union all
9 select 'C',33,'x',8 from dual union all
10 select 'C',33,'p',9 from dual union all
11 select 'D',12,'w',10 from dual union all
12 select 'D',22,'v',11 from dual
13 )
14 -- End test data
15 select
16 Col1,
17 lead(col2) over(partition by col1,col2,col3 order by null) ld,
18 col2,
19 Col3,
20 Col4
21 from t
22 order by col4
23 /
C LD COL2 C COL4
- ---------- ---------- - ----------
A 35 z 1
A 66 y 2
A 66 66 y 3
C 33 33 x 4
C 33 33 x 5
C 33 x 6
C 33 33 x 7
C 33 33 x 8
C 33 p 9
D 12 w 10
D 22 v 11
11 rows selected.
As you can see, lead function calculates following rows within partition
( specified by partition by clause) along the order by column. Because
it doesn't matter, i order simply by null. If we have duplicates within
group (group is equivalent to partition - i.e. rows 4 till 9), then we
get for all except one a value from following row in lead column and
only for last one we get NULL - because it has no following rows. Which
one is last - is undefined - because we order by NULL (i.e. random), but
this is as i mentioned meaningless, because we need only one row from
the rowset - we can take that with NULL. In the level above we sum
nullif(col2,ld) - nullif yields NULL if col2 and ld are equal ( this is
the case for all rows from previous rowset, there lead is NOT NULL) and
for only one record from all the duplicates ( where lead is NULL) - it
yields col2. So that value only will be added to the moving sum - as
intended. To understand, what actually will be added, you can also look
on the results of this query:
select
col1,sum(nullif(col2,ld)) over(partition by col1) col2,
col3,
col4,
nullif(col2,ld) null_if
from (
select
Col1,
lead(col2) over(partition by col1,col2,col3 order by null) ld,
col2,
Col3,
Col4
from t)
order by col4
Best regards
Maxim
Received on Mon Dec 10 2007 - 14:25:20 CST
