2

enter image description here

I want to do ABC analysis on this. As you see, each model has severval partID, each partID has a number of counts. As you see, the Cum_Counts is wrong when 'ties' exists.

I want to calcute Cumulated counts in each Model.

SELECT      
      Model,
      PartID,
      PartDesc,
      Counts,
      SUM(Counts) OVER (PARTITION BY Model ORDER BY Counts DESC) AS Cum_Counts,
      SUM(Counts) OVER (PARTITION BY Model) AS Total_Counts       
FROM ASL_2
ORDER BY Model
  • (... ORDER BY Counts DESC, Pk ASC) For Pk use whatever the Primary key of the table is - or any other unique column you may have. – ypercubeᵀᴹ Jun 5 '15 at 6:48
  • thanks james. I may add row number As you suggest. may I ask why my query is not working? – John Smith Jun 5 '15 at 6:56
4

The SUM() OVER does not fail, it does exactly what it is supposed to do. When you have an aggregate like SUM() with OVER (... ORDER BY something), there is a default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

This means that the sum goes over all rows that belong to the same partition (same Model in this case) and have the Counts value greater or equal to the Counts value of the current row. In case of ties, you'll see this behaviour.

To resolve ties, you have to include in the ORDER BY of the OVER part, one or more columns that can, alone or together with (Model, Counts), uniquely identify rows. So the primary key or a unique column is good. If there is a unique constraint on (Model, PartID), you could use PartID. The expression would become:

SUM(Counts) OVER (PARTITION BY Model ORDER BY Counts DESC, PartID ASC)
  AS Cumulative_Counts
  • That is Great solution. I would try it. again. Thanks. – John Smith Jun 5 '15 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.