Count Over returns Row_Number based on Order By

Question

Quick question based on some interesting syntax I've found out. I have a table with these columns:

ID INT,
DimBuyDetails_Id INT, 
WeekOfBuy INT,
Spots INT,
Retired BIT

If I run this query I will get the ROW_NUMBER() instead of the COUNT()

SELECT *
    , COUNT(ID) OVER (
        PARTITION BY DimBuyDetails_Id, WeekOfBuy
        ORDER BY ID --<
) AS ct
FROM Core.FactBuysPerWeek

However, if I change the ORDER BY clause I'll get the COUNT()

SELECT *
    , COUNT(ID) OVER (
        PARTITION BY DimBuyDetails_Id, WeekOfBuy
        ORDER BY DimBuyDetails_Id, WeekOfBuy --<Changed Order by here
) AS ct
FROM Core.FactBuysPerWeek

Anyone know what causes this?

Answer 1

If I run this query I will get the ROW_NUMBER() instead of the COUNT()

OVER Clause (Transact-SQL)

If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification.