Re: Grouping / totalling on field change

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 09 Aug 2006 15:36:04 +0200
Message-ID: <44d9e663$0$20043$9b4e6d93@newsspool4.arcor-online.net>

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 Received on Wed Aug 09 2006 - 08:36:04 CDT