Maxim Demenko wrote:
> 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
The polar flight to Seattle takes less time.
--
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 Thu Aug 10 2006 - 16:08:53 CDT
