SUM function fails when tie exist

sumwindow functions

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

Best Answer

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

Related Solutions

ORDER BY vs ROW_NUMBER for Top N Queries in PostgreSQL

First, to answer the questions implied in the comments, that the assignment of row numbers with the ROW_NUMBER() aggregate seems inefficient because we already have the COUNT(violation) numbers:

This is needed because the COUNT numbers can be different for each partition (class). Since we want the 2 top numbers (for each class), we can't find a useful condition for that. With the row numbers, we can use the WHERE v < 3 which gives us the top 2.

In version 9.3, the LATERAL joins were added in Postgres, which are similar to the CROSS and OUTER APPLY of SQL-Server. With this new kind of join, you can write a query that uses the COUNT numbers and a TOP 2 for each partition. Whether it is more or less efficient, you can test:

WITH classes AS
  ( SELECT DISTINCT class
    FROM people
  ) 
                             -- if you have a "classes" table, skip the above lines
SELECT 
    v.id, v.name, c.class,
    v.violations
FROM
    classes AS c,
  LATERAL 
    ( SELECT p.id, p.name,
             COUNT(d.violation) AS violations
      FROM people AS p
        INNER JOIN discipline AS d
          ON d.people_id = p.id
      WHERE p.class = c.class 
      GROUP BY p.id, p.name
      ORDER BY violations DESC
      LIMIT 2
  ) AS v
ORDER BY
    c.class, v.violations DESC ;

Tested at SQL-Fiddle

Mysql – To find out the sum total of GRNQty and GRNValue of particular item from column 1

To fix the error: You exposed Qty AS GRNQty; use the latter outside.

If there is a question about Q1, I missed it.

I recommend using some extra parens when doing UNION:

( SELECT ... )
UNION ALL
( SELECT ... )

Without these parens, you might add a clause (eg LIMIT) and have it mysteriously apply to the second SELECT instead of the UNION.

Best Answer

Related Solutions

ORDER BY vs ROW_NUMBER for Top N Queries in PostgreSQL

Mysql – To find out the sum total of GRNQty and GRNValue of particular item from column 1

Related Question