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
On Nov 28, 1:47 pm, 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
SELECT Count(Table.Field) AS FieldNullCount FROM Table HAVING
(((Count(Table.Field)) Is Null));
"DavidB"
news:3c1bcffc-b362-4708-b210-7987b315d838@r60g2000hsc.google groups.com...
> On Nov 28, 1:47 pm, leegold58
>> Hi,
>>
>> What is the SQL to count the number of null values in a specific
>> column in a table?
>>
>
> SELECT Count(Table.Field) 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
On Nov 28, 2:01 pm, "Sky"
> "DavidB"
>
> news:3c1bcffc-b362-4708-b210-7987b315d838@r60g2000hsc.google groups.com...
>
> > On Nov 28, 1:47 pm, leegold58
> >> Hi,
>
> >> What is the SQL to count the number of null values in a specific
> >> column in a table?
>
> > SELECT Count(Table.Field) 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.
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
leegold58
9b4d-ead1e40c56d2@j20g2000hsi.googlegroups.com:
> 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
>>
>> > SELECT Count(Table.Field) 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
On Nov 29, 9:34 pm, "Sky"
> >> > SELECT Count(Table.Field) 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
Recommended perhaps, but necessary?
SELECT School, SUM(Enrolment) AS SumEnrolment
FROM Enrolments
GROUP BY School
HAVING SUM(Enrolment)>500
ORDER BY SUM(Enrolment)
SELECT SQ.School, SQ.SumEnrolment FROM
(SELECT School, SUM(Enrolment) AS SumEnrolment
FROM Enrolments
GROUP BY School) AS SQ
WHERE SQ.SumEnrolment>500
ORDER BY SQ.SumEnrolment
I suppose I should fire up something with ShowPlan enabled to see if
they are handled identically. For right now I'll just say that I never
use "HAVING".
"lyle"
news:dc89df80-2c03-41bd-9f35-660eca81d0dd@v4g2000hsf.googleg roups.com...
>> On the other, a Having clause is necessary if you want a criterion like
>> "Having Min([MyField]) < 10"
>
> Recommended perhaps, but necessary?
>
> SELECT School, SUM(Enrolment) AS SumEnrolment
> FROM Enrolments
> GROUP BY School
> HAVING SUM(Enrolment)>500
> ORDER BY SUM(Enrolment)
>
> SELECT SQ.School, SQ.SumEnrolment FROM
> (SELECT School, SUM(Enrolment) AS SumEnrolment
> FROM Enrolments
> GROUP BY School) AS SQ
> WHERE SQ.SumEnrolment>500
> ORDER BY SQ.SumEnrolment
>
> I suppose I should fire up something with ShowPlan enabled to see if
> they are handled identically. For right now I'll just say that I never
> use "HAVING".
I might nitpick and say you are applying the "Where" clause to the result of
a nested query, not to an SQL aggregate function directly. You cannot
directly put the SQL aggregate function Sum() in a Where clause.
But your broader point is that there are many ways to do things, and I may
not imagine all of them. I would not write SQL with nested queries when not
required, but it appears to achieve the same result and probably optimizes
to the same performance.
By the way, I always appreciate your posts because they expand and tweak my
thinking. Thanks for being a contrarian, with a sense of humor. In addition
to the code and suggestions you provide for people.
- Steve