Counting Nulls as well in a select distinct...

[Offace]

Hi NewsGroup,

Have a query which selects distinct records from a column in a table given a
certain parameter. The column allows nulls and want to execute a distinct
select command on the table giving me the distinct values and a count of
them. Only issue is I get a count of 0 (zero) for null values. Here's my
query,

PARAMETERS [filterID] LONG;
SELECT DISTINCT [D].[Value], COUNT([D].[Value]) AS [Count]
FROM [tblFilterData] AS D
WHERE ([D].[FilterID] = [filterID])
GROUP BY [D].[Value]
ORDER BY [D].[Value] ASC;

This returns something like;

Value Count
------ -------
0 <---There's the nulls
1 5
2 4

What do I do to get the Null count?

Many thanks and regards,
Offace.

 

[Michel Walsh]

Hi,


COUNT(*)-COUNT(Value)



Hoping it may help,
Vanderghast, Access MVP

 

[Jamie Richards]

Alternatively, if you want text in place of the NULL you could use the NZ()
function like so:

PARAMETERS [filterID] LONG;
SELECT DISTINCT NZ([D].[Value], "Unknown"), COUNT([D].[Value]) AS [Count]
FROM [tblFilterData] AS D
WHERE ([D].[FilterID] = [filterID])
GROUP BY [D].[Value]
ORDER BY [D].[Value] ASC;

This will put a value of you choice in the hole and aggregate the field for
you.

Jamie

 

[Jamie Richards]

I was just having another look at your query. You don't normally need to
use DISTINCT if you are grouping on the field you want to be distinct.
Particularly in your case where you only have two fields, and one of them is
aggregated. Grouping elimates duplicates.

Try it without and see how you go.

Jamie

Alternatively, if you want text in place of the NULL you could use the
NZ() function like so:

PARAMETERS [filterID] LONG;
SELECT DISTINCT NZ([D].[Value], "Unknown"), COUNT([D].[Value]) AS [Count]
FROM [tblFilterData] AS D
WHERE ([D].[FilterID] = [filterID])
GROUP BY [D].[Value]
ORDER BY [D].[Value] ASC;

This will put a value of you choice in the hole and aggregate the field
for you.

Jamie

Hi NewsGroup,

Have a query which selects distinct records from a column in a table
given a certain parameter. The column allows nulls and want to execute a
distinct select command on the table giving me the distinct values and a
count of them. Only issue is I get a count of 0 (zero) for null values.
Here's my query,

PARAMETERS [filterID] LONG;
SELECT DISTINCT [D].[Value], COUNT([D].[Value]) AS [Count]
FROM [tblFilterData] AS D
WHERE ([D].[FilterID] = [filterID])
GROUP BY [D].[Value]
ORDER BY [D].[Value] ASC;

This returns something like;

Value Count
------ -------
0 <---There's the nulls
1 5
2 4

What do I do to get the Null count?

Many thanks and regards,
Offace.

 

[Offace]

You beaut guys, thanks for the the tips got it working. Thanks again..

Cheers,
Offace