Count nulls

  • Thread starter Thread starter Razor
  • Start date Start date
R

Razor

Hi,

I have a table that I'm left outer joining with another
table.

I want to query the above query to show:
count of all the records in the left table and
count of all the null values in the right table
count of all the non-null values in the right table

I tried:
SELECT [qryRITSGroupByEC-Intermediate].[Report Client],
[qryRITSGroupByEC-Intermediate].[long code], Count(nz
([RITSID])) AS RITSCount, Count(nz([Name])) AS MissingCount
FROM [qryRITSGroupByEC-Intermediate]
GROUP BY [qryRITSGroupByEC-Intermediate].[Report Client],
[qryRITSGroupByEC-Intermediate].[long code];

but it yields same value for both counts.

Thanks!

Razor
 
What is the left table and the right table? Drop the NZ clause, that replaces
the nulls with a value and count counts any non-null values.

Guessing on this

SELECT [qryRITSGroupByEC-Intermediate].[Report Client],
[qryRITSGroupByEC-Intermediate].[long code],
Count([RITSID]) AS RITSCount,
Count([Report Client]) - Count([Name]) AS MissingCount,
Count([Name]) as Non_Nulls
FROM [qryRITSGroupByEC-Intermediate]
GROUP BY [qryRITSGroupByEC-Intermediate].[Report Client],
[qryRITSGroupByEC-Intermediate].[long code];
 
Back
Top