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
Anytime you find yourself anywhere near Seattle consider this
solution an invitation to be a guest lecturer at either the
University of Washington or for the Puget Sound Oracle Users Group.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Aug 09 2006 - 13:32:38 CDT
