Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Take the following code example:

SELECT MaritalStatus,
       COUNT(*) AS CountResult
       COUNT(*) OVER() AS CountOverResult
       FROM (schema).(table)
       GROUP BY Marital Status

COUNT(*) Returns all rows ignoring nulls right?

What does COUNT(*) OVER() do?

share|improve this question
    
    
Have you read any documentation on the OVER clause? If so what wasn't clear? This site isn't a substitute for reading the documentation. –  Martin Smith Aug 25 '13 at 12:12
    
Rows cannot be null -- count(*) counts the number of rows regardless of the columns' contents. –  David Aldridge Aug 25 '13 at 14:07
    
@Martin Smith: You obviously know your stuff, but think back to when you didn't. Think back to when you were having trouble making sense of all the terminology in the documentation. Thats about where I am right now. So, instead of making presumptions, maybe try and show a little humility. I didn't understand your answer. I thought this was a site where you came if you were having trouble with database problems. You can see I am new user. I have searched for examples of the OVER() function but didn't understand them, hence, why I came here. –  David Folksman Aug 25 '13 at 19:36
    
@DavidFolksman Have you tried experimenting with count(*) over()? Just try adding it to the select list of a few different queries. You should notice a pattern that is quite obvious. Hence me wondering how much research effort you put in before asking this question. –  Martin Smith Aug 25 '13 at 19:52

1 Answer 1

up vote 3 down vote accepted

COUNT(*) Returns all rows ignoring nulls right?

I'm not sure what you mean by "ignoring nulls" here. It returns the number of rows irrespective of any NULLs

SELECT COUNT(*)       
FROM (VALUES (CAST(NULL AS INT)),
             (CAST(NULL AS INT))) V(C)

Returns 2.

Altering the above query to COUNT(C) would return 0 as when using COUNT with an expression other than * only NOT NULL values of that expression are counted.

Suppose the table in your question has the following source data

+---------+---------------+
|  Name   | MaritalStatus |
+---------+---------------+
| Albert  | Single        |
| Bob     | Single        |
| Charles | Single        |
| David   | Single        |
| Edward  | Married       |
| Fred    | Married       |
| George  | NULL          |
+---------+---------------+

The query

SELECT MaritalStatus,
       COUNT(*) AS CountResult
FROM   T
GROUP  BY MaritalStatus 

Returns

+---------------+-------------+
| MaritalStatus | CountResult |
+---------------+-------------+
| Single        |           4 |
| Married       |           2 |
| NULL          |           1 |
+---------------+-------------+

Hopefully it is obvious how that result relates to the original data.

What does COUNT(*) OVER() do?

Adding that into the SELECT list for the previous query produces

+---------------+-------------+-----------------+
| MaritalStatus | CountResult | CountOverResult |
+---------------+-------------+-----------------+
| Single        |           4 |               3 |
| Married       |           2 |               3 |
| NULL          |           1 |               3 |
+---------------+-------------+-----------------+

Notice that the result set has 3 rows and CountOverResult is 3. This is not a coincidence.

The reason for this is because it logically operates on the result set after the GROUP BY.

COUNT(*) OBER () is a windowed aggregate. The absence of any PARTITION BY or ORDER BY clause means that the window it operates on is the whole result set.

In the case of the query in your question the value of CountOverResult is the same as the the number of distinct MaritalStatus values that exist in the base table because there is one row for each of these in the grouped result.

share|improve this answer
1  
Question: What is V(C)? –  David Folksman Aug 24 '13 at 23:37
1  
@DavidFolksman - That is part of the Table Value Constructor definition it essentially defines a (derived) table called V with a single column called C and two rows. Both of which have NULL for the value of column C. It is just there to demonstrate that if you do COUNT(*) on such a table even though the rows only contain NULL they still get counted. –  Martin Smith Aug 24 '13 at 23:44
    
@MartinSmith: when did MS introduce the table value constructor? I was always under the impression it wasn't supported. –  a_horse_with_no_name Aug 25 '13 at 8:37
    
@a_horse_with_no_name - SQL Server 2008. –  Martin Smith Aug 25 '13 at 10:14
1  
@DavidFolksman - Yes. The COUNT(*) OVER () operates on the result set after joins, where, group by, and having and counts the number of rows in that. It operates on the result set before TOP though. Also windowed aggregates such as COUNT(*) OVER () can have an optional PARTITION and ORDER BY clauses which mean they only count a subset of the rows available at that stage. –  Martin Smith Aug 25 '13 at 21:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.