Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 count(*) vs count(*) over()

Author  Topic 

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 11:36:11
I'm having trouble understanding the results I get:


with data(col1) as (select 1)
select count(*) countall, count(*) over() countover
from data
where col1 <> 1


Results:

countall countover
0 1


Why does "count(*) over()" return 1?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-15 : 11:40:48
Basically OVER() is one partition applied to the entire data set, so it will always be 1.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 11:44:10
quote:
Originally posted by Lamprey

Basically OVER() is one partition applied to the entire data set, so it will always be 1.



No that's not it. It's this:

quote:
the OVER clause defines a window or user-specified set of rows within a query result set


Since the query includes count(*) (without over), there will always be one row returned. The over() clause works then on the query result, which is one row and correctly returns 1.

e.g.


with data(col1) as (select 1 union all select 2)
select count(*) countall, count(*) over() countover
from data
where col1 = 3


returns the same result set (0, 1)
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-15 : 11:56:09
I mis-typed, I meant "result set" or "resulting data set", not just "data set."
Go to Top of Page
   

- Advertisement -