group by and count null values

[Todd]

Here is the "SQL View" of the select query from Access. I wish to return a
count of null values of the records in the status field. I know of "nz" and
"iif" expressions but don't know where to put them in the design view or the
sql view. In query design the first column is the status field with group by
then sort ascending, second column is status field with count, and the last
column is submitted date data type with where and my date range. What and
where do I enter the expression?


SELECT [07517 m_eggers 1Q08].Status, Count([07517 m_eggers 1Q08].Status) AS
CountOfStatus
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#1/1/2008# And #4/19/2008#))
GROUP BY [07517 m_eggers 1Q08].Status
ORDER BY [07517 m_eggers 1Q08].Status;

 

[KARL DEWEY]

Try this ---
SELECT IIF([Status] Is Null,"Null",[Status] AS Status_All,
Count(IIF([Status] Is Null,"Null",[Status] ) AS CountOfStatus
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#1/1/2008# And #4/19/2008#))
GROUP BY [07517 m_eggers 1Q08].Status
ORDER BY [07517 m_eggers 1Q08].Status;

 

[Dale Fye]

I'm confused. You are using a field called [Submitted Date Data Type] in the
where clause, but you are comparing it to two dates. From the field name, it
doesn't look like it would be a date field.

If all you are really interested in is the NULL values, then the following
should work (assuming that the date field issue mentioned above is resolved).

SELECT COUNT(*) AS NullCount
FROM [07517 m_eggers 1Q08]
WHERE [Submitted Date Data Type]) Between #1/1/2008# And #4/19/2008#
AND [07517 m_eggers 1Q08].Status IS NULL

HTH
Dale

 

[Todd]

This query produced the desired results. The key was learning to make it a
calculated field. Thank you very much for pointing me in the right direction.

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(IIf(IsNull([status]),"Pending",[Status])) AS [Status Count]
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#4/13/2008# And #4/19/2008#))
GROUP BY IIf(IsNull([status]),"Pending",[Status])
ORDER BY IIf(IsNull([status]),"Pending",[Status]);

Try this ---
SELECT IIF([Status] Is Null,"Null",[Status] AS Status_All,
Count(IIF([Status] Is Null,"Null",[Status] ) AS CountOfStatus
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#1/1/2008# And #4/19/2008#))
GROUP BY [07517 m_eggers 1Q08].Status
ORDER BY [07517 m_eggers 1Q08].Status;

--
KARL DEWEY
Build a little - Test a little

Here is the "SQL View" of the select query from Access. I wish to return a
count of null values of the records in the status field. I know of "nz" and
"iif" expressions but don't know where to put them in the design view or the
sql view. In query design the first column is the status field with group by
then sort ascending, second column is status field with count, and the last
column is submitted date data type with where and my date range. What and
where do I enter the expression?


SELECT [07517 m_eggers 1Q08].Status, Count([07517 m_eggers 1Q08].Status) AS
CountOfStatus
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#1/1/2008# And #4/19/2008#))
GROUP BY [07517 m_eggers 1Q08].Status
ORDER BY [07517 m_eggers 1Q08].Status;

 

[Todd]

Thank you for your input. I am new to doing more complex queries and I don't
quite understand everything people are telling me. I don't know how to plug
your suggestion in my query where I am grouping and counting the different
status for a given date range. The query below worked nicely. The key for
me was learning about calculated fields.

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(IIf(IsNull([status]),"Pending",[Status])) AS [Status Count]
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#4/13/2008# And #4/19/2008#))
GROUP BY IIf(IsNull([status]),"Pending",[Status])
ORDER BY IIf(IsNull([status]),"Pending",[Status]);

I'm confused. You are using a field called [Submitted Date Data Type] in the
where clause, but you are comparing it to two dates. From the field name, it
doesn't look like it would be a date field.

If all you are really interested in is the NULL values, then the following
should work (assuming that the date field issue mentioned above is resolved).

SELECT COUNT(*) AS NullCount
FROM [07517 m_eggers 1Q08]
WHERE [Submitted Date Data Type]) Between #1/1/2008# And #4/19/2008#
AND [07517 m_eggers 1Q08].Status IS NULL

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Here is the "SQL View" of the select query from Access. I wish to return a
count of null values of the records in the status field. I know of "nz" and
"iif" expressions but don't know where to put them in the design view or the
sql view. In query design the first column is the status field with group by
then sort ascending, second column is status field with count, and the last
column is submitted date data type with where and my date range. What and
where do I enter the expression?


SELECT [07517 m_eggers 1Q08].Status, Count([07517 m_eggers 1Q08].Status) AS
CountOfStatus
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#1/1/2008# And #4/19/2008#))
GROUP BY [07517 m_eggers 1Q08].Status
ORDER BY [07517 m_eggers 1Q08].Status;

 

[Dale Fye]

Todd,

In your query, you indicated that you wanted to return a count of the number
of NULL values. The query I wrote would do just that, but would not return
the values of the other statuses.

Personally, if you wanted a count of all of the statuses, your initial query
would have given that to you, but the first entry would have had an empty
cell in the first column.

I recommend you change the first two lines to:

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(*) AS [Status Count]

This should run quicker than than the other one.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Thank you for your input. I am new to doing more complex queries and I don't
quite understand everything people are telling me. I don't know how to plug
your suggestion in my query where I am grouping and counting the different
status for a given date range. The query below worked nicely. The key for
me was learning about calculated fields.

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(IIf(IsNull([status]),"Pending",[Status])) AS [Status Count]
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#4/13/2008# And #4/19/2008#))
GROUP BY IIf(IsNull([status]),"Pending",[Status])
ORDER BY IIf(IsNull([status]),"Pending",[Status]);

I'm confused. You are using a field called [Submitted Date Data Type] in the
where clause, but you are comparing it to two dates. From the field name, it
doesn't look like it would be a date field.

If all you are really interested in is the NULL values, then the following
should work (assuming that the date field issue mentioned above is resolved).

SELECT COUNT(*) AS NullCount
FROM [07517 m_eggers 1Q08]
WHERE [Submitted Date Data Type]) Between #1/1/2008# And #4/19/2008#
AND [07517 m_eggers 1Q08].Status IS NULL

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Here is the "SQL View" of the select query from Access. I wish to return a
count of null values of the records in the status field. I know of "nz" and
"iif" expressions but don't know where to put them in the design view or the
sql view. In query design the first column is the status field with group by
then sort ascending, second column is status field with count, and the last
column is submitted date data type with where and my date range. What and
where do I enter the expression?


SELECT [07517 m_eggers 1Q08].Status, Count([07517 m_eggers 1Q08].Status) AS
CountOfStatus
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#1/1/2008# And #4/19/2008#))
GROUP BY [07517 m_eggers 1Q08].Status
ORDER BY [07517 m_eggers 1Q08].Status;

 

[Todd]

Dale,
I tried what you suggested and it worked great. I knew I needed to have the
status field twice (one for count and once for groupby) but the "IIF"
logically didn't make sense to have twice because of the count(*) expression
we can use. Luckily for me it worked anyway. I do understand the importance
of a lean fast database and your solution makes way more sense. Thank you
for the extra insight into this.

Todd,

In your query, you indicated that you wanted to return a count of the number
of NULL values. The query I wrote would do just that, but would not return
the values of the other statuses.

Personally, if you wanted a count of all of the statuses, your initial query
would have given that to you, but the first entry would have had an empty
cell in the first column.

I recommend you change the first two lines to:

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(*) AS [Status Count]

This should run quicker than than the other one.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Thank you for your input. I am new to doing more complex queries and I don't
quite understand everything people are telling me. I don't know how to plug
your suggestion in my query where I am grouping and counting the different
status for a given date range. The query below worked nicely. The key for
me was learning about calculated fields.

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(IIf(IsNull([status]),"Pending",[Status])) AS [Status Count]
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#4/13/2008# And #4/19/2008#))
GROUP BY IIf(IsNull([status]),"Pending",[Status])
ORDER BY IIf(IsNull([status]),"Pending",[Status]);

I'm confused. You are using a field called [Submitted Date Data Type] in the
where clause, but you are comparing it to two dates. From the field name, it
doesn't look like it would be a date field.

If all you are really interested in is the NULL values, then the following
should work (assuming that the date field issue mentioned above is resolved).

SELECT COUNT(*) AS NullCount
FROM [07517 m_eggers 1Q08]
WHERE [Submitted Date Data Type]) Between #1/1/2008# And #4/19/2008#
AND [07517 m_eggers 1Q08].Status IS NULL

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Here is the "SQL View" of the select query from Access. I wish to return a
count of null values of the records in the status field. I know of "nz" and
"iif" expressions but don't know where to put them in the design view or the
sql view. In query design the first column is the status field with group by
then sort ascending, second column is status field with count, and the last
column is submitted date data type with where and my date range. What and
where do I enter the expression?


SELECT [07517 m_eggers 1Q08].Status, Count([07517 m_eggers 1Q08].Status) AS
CountOfStatus
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#1/1/2008# And #4/19/2008#))
GROUP BY [07517 m_eggers 1Q08].Status
ORDER BY [07517 m_eggers 1Q08].Status;