SQL to count the number of nulls in a column

[leegold58]

Hi,

What is the SQL to count the number of null values in a specific
column in a table?

I knw this must be simple but I've been Googling this for a long time
and have not the SQL to do this.

Thanks for the support,

Lee

[DavidB]

On Nov 28, 1:47 pm, leegold58 <goldt...@world post.comwrote:

Hi,

What is the SQL to count the number of null values in a specific
column in a table?

I knw this must be simple but I've been Googling this for a long time
and have not the SQL to do this.

Thanks for the support,

Lee

SELECT Count(Table.Fie ld) AS FieldNullCount FROM Table HAVING
(((Count(Table. Field)) Is Null));

[Sky]

"DavidB" <je***@yahoo.co mwrote in message
news:3c******** *************** ***********@r60 g2000hsc.google groups.com...

On Nov 28, 1:47 pm, leegold58 <goldt...@world post.comwrote:

>Hi,

What is the SQL to count the number of null values in a specific
column in a table?

>
SELECT Count(Table.Fie ld) AS FieldNullCount FROM Table HAVING
(((Count(Table. Field)) Is Null));

I don't think so.

I suggest:
Select Count(*) From [MyTable] Where [MyField] Is Null

- Steve

[DavidB]

On Nov 28, 2:01 pm, "Sky" <sky @ stanley associates . comwrote:

"DavidB" <[e-mail] mwrote in message

news:3c******** *************** ***********@r60 g2000hsc.google groups.com...

On Nov 28, 1:47 pm, leegold58 <goldt...@world post.comwrote:

Hi,

What is the SQL to count the number of null values in a specific
column in a table?

SELECT Count(Table.Fie ld) AS FieldNullCount FROM Table HAVING
(((Count(Table. Field)) Is Null));

I don't think so.

I suggest:
Select Count(*) From [MyTable] Where [MyField] Is Null

- Steve

Same results. What I posted was what query builder generated. Only
difference is that mine adds an AS clause that will name the output
field whatever you want it named.

[Ron2006]

If the field you are counting is null, then the count does not work on
that field. Pick a different field to count that will ALWAYS have a
value.

Ron

[Bob Quintal]

leegold58 <go******@world post.comwrote in news:f4528c63-803d-4e01-
9b************* **@j20g2000hsi. googlegroups.co m:

Hi,

What is the SQL to count the number of null values in a specific
column in a table?

I knw this must be simple but I've been Googling this for a long time
and have not the SQL to do this.

Thanks for the support,

Lee

SELECT sum(iif(isnull( table.field),1, 0) as nullCount from table;

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Sky]

>>

SELECT Count(Table.Fie ld) AS FieldNullCount FROM Table HAVING
(((Count(Table. Field)) Is Null));

Select Count(*) From [MyTable] Where [MyField] Is Null

I was referring to the "Having" clause, not the "As" renaming.
But my prior comment "I don't think so" was inappropriate.

The aggregate "Having" clause is necessary for a criterion applied to an
aggregate function such as Count(), but the query engine must first select
and process all records, and only then evaluate the aggregate function
(Count in this case) against the criterion at the end.

The "Where" clause performs the filtering during the initial record scan
prior to computing any aggregate functions, so it is more efficient. In
general, a Where clause is preferred over a Having clause. The difference is
negligible for small tables, but Where is recommended when possible.

On the other, a Having clause is necessary if you want a criterion like
"Having Min([MyField]) < 10", but it is not needed here for a filtering
criterion such as Is Null.

- Steve