DA Morgan schrieb:
> 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.
Thank you for that nice invitation Daniel.
I don't think i manage to make use of it, but 'll try at least to come
to San Francisco this year... (well, to be honest, i say to myself it
almost everyear ;-))
Best regards
Maxim
Received on Thu Aug 10 2006 - 13:49:58 CDT
