SUM() OVER does not fail, it does exactly what it is supposed to do. When you have an aggregate like
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)