CODEWITHSUNDEEP
sqlsql-server-2008t-sql
actkatiemacias
actkatiemacias

Reputation: 1473

Why do I get 0 for Count([Field]) when null

I have the following query:

Select [Field], count([Field]) as Counts
from [Table_Name]
group by [Field]

The results look like the following:

[Field] [Counts]
Type1   100
Type2   100
Type3   100
Type4   100
Null    0

However when I count on a Key or any other field than the field I'm grouping on I get the actual count of rows with [Field] Null. Why is that?

Select [Field], count([Other]) as Counts
from [Table_Name]
group by [Field]

Result:

[Field] [Counts]
Type1   100
Type2   100
Type3   100
Type4   100
Null    100

Upvotes: 0

Views: 102

Answers (2)

PM 77-1
PM 77-1

Reputation: 13334

You may want to experiment with my SQL Fiddle.

As currently configured (you can make any changes you want) for the following data:

FIELD1  OTHER
AA      (null)
BB         O1
BB         O2
AA         A1
(null)     N3
(null)  (null)

the following query that incorporates all three COUNT options for this particular table:

SELECT [Field1], COUNT([Field1]) [Fields], 
                 COUNT([Other]) [Others], 
                 COUNT(*) [Records]
FROM [Table1]
GROUP BY [Field1]

produces the following results:

FIELD1  FIELDS  OTHERS  RECORDS
(null)     0        1      2
AA         2        1      2
BB         2        2      2

Upvotes: 0

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

That's how COUNT works. When you specify column NULL values are eliminated from calculation. And because your COUNT is set on the same column as GROUP BY, you receive 0 for NULL group - all values you're counting are NULL and they are all skipped.

You can make it work with following code:

Select [Field], count(ISNULL([Field], 0)) as Counts
from [Table_Name]
group by [Field]

Or maybe simpler:

Select [Field], count(*) as Counts
from [Table_Name]
group by [Field]

Upvotes: 3

Related Questions