Query to Count, but return items that are null

[Sandy]

Hello -

Didn't know how to describe this in the subject, but I need a query that
counts the number of items in certain categories, but if that category is
blank, I want it to return a zero. I need it for a report.

All I seem to be able to get is the count of records that are greater than
zero, and I need a listing of all categories regardless of whether they're
zero (or null), along with how many in each category.

Any suggestions will be appreciated!

 

[Marshall Barton]

Didn't know how to describe this in the subject, but I need a query that
counts the number of items in certain categories, but if that category is
blank, I want it to return a zero. I need it for a report.

All I seem to be able to get is the count of records that are greater than
zero, and I need a listing of all categories regardless of whether they're
zero (or null), along with how many in each category.

You will need a table that has a record for each possible
category. Then you can use an outer join to the data you
are counting.

 

[Sandy]

Thanks for responding, Barton.

I used the following query:

SELECT tblDeclaration.Declaration, Count(*) AS NumOfDecl
FROM tblDeclaration Left JOIN tblCalls ON tblDeclaration.DeclarationID =
tblCalls.DeclarationID
GROUP BY tblDeclaration.Declaration;

It still isn't returning any rows that are less than 1. Any idea what I'm
doing wrong? Also, I have to manage to use another column from tblCalls -
CallDate, in addition to getting Count, because I need the count of each
Declaration between two dates. [By the way, tblCalls is the Main table.]

What am I doing wrong in the above query?

 

[Sandy]

I just tried the following -

SELECT tblDeclaration.Declaration, Count(*) AS NumOfDecl
FROM qryOne RIGHT JOIN tblDeclaration ON qryOne.DeclarationID =
tblDeclaration.DeclarationID
GROUP BY tblDeclaration.Declaration

qryOne is a regular join between tblCalls and tblDeclaration. I ran a query
using tomorrow's date, just to see if it would return 23 rows, all with 0 and
it returned 23 rows, but each with a 1 as the NumOfDecl. [There are no
entries yet for tomorrow.] What could be causing that?

 

[John Spencer]

Count counts all the non-null values.
Therefore Count(*) will return a count of the total number of aggregated
rows. Since you are forcing a row to be returned, the count will be 1

Try Count(tblDeclaration.DeclarationID) since that value can be null

I just tried the following -

SELECT tblDeclaration.Declaration, Count(*) AS NumOfDecl
FROM qryOne RIGHT JOIN tblDeclaration ON qryOne.DeclarationID =
tblDeclaration.DeclarationID
GROUP BY tblDeclaration.Declaration

qryOne is a regular join between tblCalls and tblDeclaration. I ran a
query
using tomorrow's date, just to see if it would return 23 rows, all with 0
and
it returned 23 rows, but each with a 1 as the NumOfDecl. [There are no
entries yet for tomorrow.] What could be causing that?
--
Sandy

You will need a table that has a record for each possible
category. Then you can use an outer join to the data you
are counting.

 

[Marshall Barton]

I used the following query:

SELECT tblDeclaration.Declaration, Count(*) AS NumOfDecl
FROM tblDeclaration Left JOIN tblCalls ON tblDeclaration.DeclarationID =
tblCalls.DeclarationID
GROUP BY tblDeclaration.Declaration;

It still isn't returning any rows that are less than 1. Any idea what I'm
doing wrong? Also, I have to manage to use another column from tblCalls -
CallDate, in addition to getting Count, because I need the count of each
Declaration between two dates. [By the way, tblCalls is the Main table.]

I think the issue is that you are using Count(*). That will
count all the rows, regardless of their content. Try
changing it to:
. . ., Count(tblCalls.DeclarationID) AS NumOfDecl

 

[Sandy]

Thanks, Marshall!
--
Sandy

I used the following query:

SELECT tblDeclaration.Declaration, Count(*) AS NumOfDecl
FROM tblDeclaration Left JOIN tblCalls ON tblDeclaration.DeclarationID =
tblCalls.DeclarationID
GROUP BY tblDeclaration.Declaration;

It still isn't returning any rows that are less than 1. Any idea what I'm
doing wrong? Also, I have to manage to use another column from tblCalls -
CallDate, in addition to getting Count, because I need the count of each
Declaration between two dates. [By the way, tblCalls is the Main table.]

I think the issue is that you are using Count(*). That will
count all the rows, regardless of their content. Try
changing it to:
. . ., Count(tblCalls.DeclarationID) AS NumOfDecl

 

[Sandy]

Thanks, John!
--
Sandy

Count counts all the non-null values.
Therefore Count(*) will return a count of the total number of aggregated
rows. Since you are forcing a row to be returned, the count will be 1

Try Count(tblDeclaration.DeclarationID) since that value can be null

I just tried the following -

SELECT tblDeclaration.Declaration, Count(*) AS NumOfDecl
FROM qryOne RIGHT JOIN tblDeclaration ON qryOne.DeclarationID =
tblDeclaration.DeclarationID
GROUP BY tblDeclaration.Declaration

qryOne is a regular join between tblCalls and tblDeclaration. I ran a
query
using tomorrow's date, just to see if it would return 23 rows, all with 0
and
it returned 23 rows, but each with a 1 as the NumOfDecl. [There are no
entries yet for tomorrow.] What could be causing that?
--
Sandy

Sandy wrote:

Didn't know how to describe this in the subject, but I need a query that
counts the number of items in certain categories, but if that category
is
blank, I want it to return a zero. I need it for a report.

All I seem to be able to get is the count of records that are greater
than
zero, and I need a listing of all categories regardless of whether
they're
zero (or null), along with how many in each category.


You will need a table that has a record for each possible
category. Then you can use an outer join to the data you
are counting.