Counting Nulls as well in a select distinct...

  • Thread starter Thread starter Offace
  • Start date Start date
O

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.
 
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
 
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

Jamie Richards said:
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

Offace said:
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.
Click to expand...
Click to expand...
 
Back
Top