# SUM function fails when tie exist 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

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)