Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Grouping / totalling on field change

Re: Grouping / totalling on field change

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 9 Aug 2006 07:34:43 -0700
Message-ID: <1155134082.947355.297360@b28g2000cwb.googlegroups.com>

Maxim Demenko wrote:
> Carlos schrieb:
> > Ture Magnusson wrote:
> >> Hello all! I cannot figure out if/how I can do this with an SQL query.
> >>
> >> I would like a result which totals the weight for each quality whenever
> >> the quality changes (based on the order of the ID field).
> >>
> >> Original data:
> >> ID, QUALITY, WEIGHT
> >> 1, A, 10
> >> 2, A, 11
> >> 3, A, 12
> >> 4, B, 11
> >> 5, B, 19
> >> 6, A, 9
> >> 7, A, 14
> >> 8, C, 4
> >> 9, C, 7
> >>
> >> Desired result:
> >> ENDID, QUALITY, WEIGHT
> >> 3, A, 34
> >> 5, B, 30
> >> 7, A, 23
> >> 9, C, 11
> >>
> >> Best regards,
> >> Ture Magnusson
> >> Karlstad, Sweden
> >
> > One more time:
> >
> > The Wise Man (TK) says: "Analytics rock, analytics roll".
> >
> > Cheers.
> >
> > Carlos.
> >
>
> This one works for me:
> cms_at_ORA102> set echo on
> cms_at_ORA102> CREATE TABLE NG(ID,QUALITY,WEIGHT) NOLOGGING AS
> 2 SELECT 1,'A',10 FROM DUAL UNION ALL
> 3 SELECT 2,'A',11 FROM DUAL UNION ALL
> 4 SELECT 3,'A',12 FROM DUAL UNION ALL
> 5 SELECT 4,'B',11 FROM DUAL UNION ALL
> 6 SELECT 5,'B',19 FROM DUAL UNION ALL
> 7 SELECT 6,'A',9 FROM DUAL UNION ALL
> 8 SELECT 7,'A',14 FROM DUAL UNION ALL
> 9 SELECT 8,'C',4 FROM DUAL UNION ALL
> 10 SELECT 9,'C',7 FROM DUAL;
>
> Table created.
>
> cms_at_ORA102>
> cms_at_ORA102> SELECT MAX(Id) Id, MAX(Quality) Quality, SUM(Weight) Weight
> 2 FROM (SELECT Id, Quality, Weight, SUM(New_Seq) Over(ORDER BY Id)
> New_Group
> 3 FROM (SELECT Id,
> 4 Quality,
> 5 Weight,
> 6 Decode(Lag(Quality) Over(ORDER BY Id),
> Quality, 0, Id) New_Seq
> 7 FROM Ng))
> 8 GROUP BY New_Group
> 9 ORDER BY 1;
>
> ID Q WEIGHT
> ---------- - ----------
> 3 A 33
> 5 B 30
> 7 A 23
> 9 C 11
>
>
> Best regards
>
> Maxim

Nice. I will have to remember the use of decode (or maybe case) to flag the change in value.

Received on Wed Aug 09 2006 - 09:34:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US