Re: ignore use group by for some column

On Dec 10, 11:25 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> nova1..._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- Hide quoted text -
>
> - Show quoted text -

Its clear now

"lead": It gets all within group except last one we get null, and last row dependent on the order. But in our case the order is not important. (Get repeating)
nullif: After that we get the opposite that repeating (all except repeating).
sum: Finally, summation for not repeating

Right.

Thank you very much. Received on Tue Dec 11 2007 - 02:49:57 CST