group by and count null values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Todd - 17 Apr 2008 21:34 GMT

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;

Reply to this Message

KARL DEWEY - 17 Apr 2008 22:30 GMT

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;

Signature

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
[quoted text clipped - 11 lines]
> GROUP BY [07517 m_eggers 1Q08].Status
> ORDER BY [07517 m_eggers 1Q08].Status;

Reply to this Message

Todd - 18 Apr 2008 16:45 GMT

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,
[quoted text clipped - 20 lines]
> > GROUP BY [07517 m_eggers 1Q08].Status
> > ORDER BY [07517 m_eggers 1Q08].Status;

Reply to this Message

Dale Fye - 18 Apr 2008 13:14 GMT

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

Signature

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

email address is invalid
Please reply to newsgroup only.

Reply to this Message

Todd - 18 Apr 2008 16:51 GMT

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
[quoted text clipped - 26 lines]
> > GROUP BY [07517 m_eggers 1Q08].Status
> > ORDER BY [07517 m_eggers 1Q08].Status;

Reply to this Message

Dale Fye - 18 Apr 2008 17:04 GMT

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

Signature

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
[quoted text clipped - 40 lines]
> > > GROUP BY [07517 m_eggers 1Q08].Status
> > > ORDER BY [07517 m_eggers 1Q08].Status;

Reply to this Message

Todd - 18 Apr 2008 18:11 GMT

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,
>
[quoted text clipped - 59 lines]
> > > > GROUP BY [07517 m_eggers 1Q08].Status
> > > > ORDER BY [07517 m_eggers 1Q08].Status;

Reply to this Message