Calculate Null Field

  • Thread starter Thread starter zyus
  • Start date Start date
Z

zyus

In my query i hv few fields as per ex below:

Field1
Field2
Field3
Field4

How to calculate number and % of null value in each field. Expected results
will be

Field1 200 4%
Field2 1000 20%
Field3 500 10%
Field4 1500 30%

assuming i hv 5000 rows of record
 
On Wed, 14 May 2008 20:56:01 -0700, zyus

You probably have to run multiple queries. Here is one that works in
the Northwind sample database and gets the number of orders with a
null for the shipped date:

SELECT Count(Orders.[Order ID]) AS [CountOfOrder ID]
FROM Orders
WHERE (((Orders.[Shipped Date]) Is Null));

Note how I'm getting the count of the primary key column, NOT the
Shipped Date column.

-Tom.
 
zyus said:
In my query i hv few fields as per ex below:

Field1
Field2
Field3
Field4

How to calculate number and % of null value in each field. Expected
results
will be

Field1 200 4%
Field2 1000 20%
Field3 500 10%
Field4 1500 30%

assuming i hv 5000 rows of record
Click to expand...


Try something like:

SELECT
Count(*) AS TotalRecs,
Count(*)-Count([Field1]) AS Field1CountNull,
(Count(*)-Count([Field1]))/Count(*) AS Field1PctNull,
Count(*)-Count([Field2]) AS Field2CountNull,
(Count(*)-Count([Field2]))/Count(*) AS Field2PctNull,
Count(*)-Count([Field3]) AS Field3CountNull,
(Count(*)-Count([Field3]))/Count(*) AS Field3PctNull,
Count(*)-Count([Field4]) AS Field4CountNull,
(Count(*)-Count([Field4]))/Count(*) AS Field4PctNull
FROM YourTableOrQuery;

You'll want to either apply the Percent format to the PctNull fields, or
else adjust them to percentages by multiplying their values by 100.
 
Back
Top