0

I am currently reading on a procedure created by someone else and I don't understand this line of code:

TotalRecordsCount = COUNT(Id) OVER().

Usually people use partitioned by in OVER(). If you don't pass in parameters in OVER(), what does it do?

Qiu
  • 5,651
  • 10
  • 49
  • 56
Simon Lin
  • 3
  • 1

1 Answers1

0

I don't think the question flagged as duplicate addresses what happens in this specific case where over() is used without arguments so I'll post an answer despite the dupe flags...

For some window functions the partition and order by clauses are optional; count being one on them as applying count to a variable without partitioning makes perfect sense - the count will apply over the entire set - whereas a function like rank or row_number won't make any sense whatsoever without some kind of order.

Quoting the reference documentation:

If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

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.

jpw
  • 44,361
  • 6
  • 66
  • 86
  • Thanks for you help! But in this case can I just delete the over() clause? – Simon Lin Aug 11 '15 at 00:02
  • @SimonLin Maybe, but without the over clause you need to group by all other referenced columns that are not aggregated and that might change the meaning of the query. So the answer is: it depends on what you are trying to do. – jpw Aug 11 '15 at 00:05
  • Thanks again! You solve my problems! – Simon Lin Aug 11 '15 at 05:35