count(*) vs count(*) over() - SQL Server Forums

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.

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)

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."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources