# What does this SUM OVER code do?

 Once upon a time I wrote this code... today my Sustaining Engineer asked "what does it do?" and alas, I am at a loss for words: ```SELECT rroad_ranked_table.table_id, CAST ( SUM ( rroad_ranked_table.row_count ) OVER ( ORDER BY rroad_ranked_table.row_count DESC, rroad_ranked_table.table_id ) -- OVER ( ORDER BY must be unique AS DECIMAL ( 15, 4 ) ) AS sum_row_count, sum_row_count / CAST ( ( SELECT SUM ( row_count ) FROM rroad_ranked_table ) AS DECIMAL ( 15, 4 ) ) AS fraction INTO #temp_row_fraction FROM rroad_ranked_table; ``` Here's the table: ```DECLARE LOCAL TEMPORARY TABLE rroad_ranked_table ( table_id UNSIGNED INT NOT NULL, row_fraction DECIMAL ( 15, 4 ) NOT NULL DEFAULT ( 0.0 ), row_count UNSIGNED BIGINT NOT NULL, size_fraction DECIMAL ( 15, 4 ) NOT NULL DEFAULT ( 0.0 ), size_in_bytes UNSIGNED BIGINT NOT NULL, CONSTRAINT PRIMARY KEY ( table_id ) ) NOT TRANSACTIONAL; ``` asked 19 Mar '10, 13:37 Breck Carter 30.9k●498●682●993 accept rate: 20%

 From the help, you'll know that "If the window specification contains an ORDER BY clause, it is equivalent to specifying RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW." Aside: my recommedation is to always specify either ROWS or RANGE in a window so that the intent of the computation is clear. So the window aggregate SUM() is hence computing a cumulative sum for each row in rroad_ranked_table. The third SELECT list expression then computes the fraction of this cumulative sum over the sum of all of the row counts. Does that help? answered 19 Mar '10, 14:07 Glenn Paulley 10.8k●5●76●106 accept rate: 43% Comment Text Removed Yes, it helps a great deal! Thank you! Breck, give Glenn a million bounty points!!! 1 @Glenn: My mental roadblock was "why on earth would I want a cumulative fraction?"... then I thought, "Glenn must be wrong"... then I realized "That's absurd!" (the Glenn must be wrong part, not the cumulative fraction part). Turns out this is a teeny part of the logic behind Foxhound's Largest Tables list which ranks the tables that account for at least 80% of the rows and 80% of the bytes... hence the ranking AND the summing. Not the funkiest code in Foxhound, but close... calculating the database version number, that wins funkiest. @Margaret: Glenn is Number One in points and will probably stay there without any bounties :)
 I really should know better than to quibble with Glenn, but here are a few comments anyhow: First, the following statement from the help and Glenn's answer is not correct: "If the window specification contains an ORDER BY clause, it is equivalent to specifying RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW." The RANGE unit can only be used when: The ORDER BY clause contains a single element The single element of the ORDER BY if of datetime type or a number type If you have an OVER clause with an ORDER BY with two elements, the you are not allowed to use RANGE units (you get an error Composite ORDER BY not allowed with RANGE SQLCODE=-966). In Breck's example, you could not use RANGE because there are two order-by elements. In other queries, the order by element could be a string. Aha, you may be thinking, RANGE is clearly the wrong unit and we should instead use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Unfortunately the semantics are not the same when there are ties in the ORDER BY column. Consider the following: ```create table T_OverExample(pk int, x int, y double); insert into T_OverExample select row_num as pk, pk/2 as x, pk as y from sa_rowgenerator(0,10); select * , sum(y) over(order by x) sum , sum(y) over(order by x rows between unbounded preceding and current row) sumrows , sum(y) over(order by x,x range between unbounded preceding and current row) sumrange from T_OverExample; ``` In this example, we have `sum` and `sumrange` always equal. Since there are duplicate `x` values, the sum is computed over a group with a single x value, and the sum is returned for all rows in the group. The `sumrows` column is computed using ROWS units and the result is different. Even if there are tie groups according to the ORDER BY, the SUM is computed cumulatively with each row generating a new value. If there are tie groups, the order within the group is non-deterministic(*). SQL Anywhere does not support a relatively new unit of GROUPS. When an ORDER BY is specified but the window does not define bounds, then the default bounds are actually GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The only real difference is that GROUPS does not put any restrictions on the number or types of elements in the ORDER BY. A second comment is that in Breck's formulation there is a subquery to compute the total sum--usually this would be done as a second OVER clause as follows: ```select pk, sum(y) over(order by x)/sum(y) over() cumulative_frac from T_OverExample; ``` In many cases there wont be a huge performance difference but this approach is really important if you have a PARTITION BY -- you want the fraction within the partition instead of the entire table. (*) If the ORDER BY in an OVER clause is not unique, then the ordering is non-deterministic. Nevertheless, if there are two window functions with an identical ORDER BY, the server is required to choose the same ordering for both. So, if you were worrying about that detail, that one's ok. answered 19 Mar '14, 14:38 Ivan T. Bowman 2.8k●2●27●32 accept rate: 39%
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

question asked: 19 Mar '10, 13:37

question was seen: 1,990 times

last updated: 19 Mar '14, 14:38