Re: Grouping / totalling on field change

Carlos wrote:
> 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.

Ture, are you saying that if the value of Quality changes that you want a total but you do not want to include any value for Quality that appear later in the table as part of the total? In other words are you sure your answer should not be:

        F1 F2 F3
---------- - ----------

         7 A         56
         5 B         30
         9 C         11

which you can get using

select max(f1) F1, f2, sum(f3) as F3
from t
group by f2
/

I expect you need to resort to pl/sql to solve this problem since I cannot think of a way to partition the data that identies the breaks though someone else might.

HTH -- Mark D Powell -- Received on Wed Aug 09 2006 - 09:13:34 CDT