sql – TSQL OVER clause: COUNT(*) OVER (ORDER BY a)

sql – TSQL OVER clause: COUNT(*) OVER (ORDER BY a)

It gives a running total (this functionality was not implemented in SQL Server until version 2012.)

The ORDER BY defines the window to be aggregated with UNBOUNDED PRECEDING and CURRENT ROW as the default when not specified. SQL Server defaults to the less well performing RANGE option rather than ROWS.

They have different semantics in the case of ties in that the window for the RANGE version includes not just the current row (and preceding rows) but also any additional tied rows with the same value of a as the current row. This can be seen in the number of rows counted by each in the results below.

SELECT  a, 
        b,
        COUNT(*) OVER (ORDER BY a 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS  [Rows],
        COUNT(*) OVER (ORDER BY a 
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range],
        COUNT(*) OVER() AS [Over()]
    FROM    t;

Returns

a        b        Rows        Range       Over()
-------- -------- ----------- ----------- -----------
NULL     NULL     1           4           12
NULL     NULL     2           4           12
NULL     NULL     3           4           12
NULL     NULL     4           4           12
a        b        5           7           12
a        b        6           7           12
a        b        7           7           12
c        d        8           11          12
c        d        9           11          12
c        d        10          11          12
c        d        11          11          12
e        NULL     12          12          12

To achieve the result that you were expecting to get omit both the PARTITION BY and ORDER BY and use an empty OVER() clause (also shown above).

If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as the default for window frame
So what does that mean, lets focus on UNBOUNDED PRECEDING AND CURRENT ROW. This gives a running total from the starting row to the current row.
But in case if you want to have an overall count then you can also specify

UNBOUNDED PRECEDING AND UNBOUNDED Following
This considers entire data set and Over() is just a shortcut of this

    select a,b,
count(*) over(order by a) as [count],
COUNT(*) OVER (ORDER BY a 
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range],
COUNT(*) OVER (ORDER BY a 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS  [Rows],
COUNT(*) OVER (ORDER BY a 
                         RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED Following) AS [Range_Unbounded_following],
COUNT(*) OVER (ORDER BY a 
                         ROWs BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED Following) AS [Row_Unbounded_following]
,COUNT(*) OVER () AS [Plain_over]
from t 
order by [count]

Result is

a        b        count       Range       Rows        Range_Unbounded_following Row_Unbounded_following Plain_over
-------- -------- ----------- ----------- ----------- ------------------------- ----------------------- -----------
NULL     NULL     4           4           1           12                        12                      12
NULL     NULL     4           4           2           12                        12                      12
NULL     NULL     4           4           3           12                        12                      12
NULL     NULL     4           4           4           12                        12                      12
a        b        7           7           5           12                        12                      12
a        b        7           7           6           12                        12                      12
a        b        7           7           7           12                        12                      12
c        d        11          11          8           12                        12                      12
c        d        11          11          9           12                        12                      12
c        d        11          11          10          12                        12                      12
c        d        11          11          11          12                        12                      12
e        NULL     12          12          12          12                        12                      12

sql – TSQL OVER clause: COUNT(*) OVER (ORDER BY a)

Leave a Reply

Your email address will not be published.