Solved

How to include NULL values in Group Count

Posted on 2010-11-16
3
665 Views
Last Modified: 2012-05-10
Experts,

How can I create a Query that groups on a field and counts NULL values as well.

Tier 2     62
Tier 3     15

That's all. However there are 134 records. I would like the query to list:
Tier 2     62
Tier 3     15
Blank      57

I tried linking to a query with:

SELECT Count(*) AS NoTier
FROM tblStudent
WHERE (((tblStudent.tblStdTier) Is Null));

But because I am grouping my results are

Tier 2     62     57
Tier 3     15     57

I really need:
Tier 2     62
Tier 3     15
Blank      57

Because I am displaying the results in a continous form on a statistics page.

Thanks!



SELECT tblStudent.tblStdTier AS Tier, Count(tblStudent.tblStdTier) AS [Count]

FROM tblStudent

GROUP BY tblStudent.tblStdTier

HAVING (((tblStudent.tblStdTier)<>"0"));

Open in new window

0
Comment
Question by:shogun5
  • 2
3 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

try this


SELECT iif([tblStdTier] is null, "NoTier",[tblstdtier]) AS Tier, Count(ID) AS [Count]
FROM tblStudent
GROUP BY iif([tblStdTier] is null, "NoTier",[tblstdtier])
HAVING (((iif([tblStdTier] is null, "NoTier",[tblstdtier]))<>"0"));
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
or this

SELECT iif([tblStdTier] is null, "NoTier",[tblstdtier]) AS Tier, Count(*) AS [Count]
FROM tblStudent
GROUP BY iif([tblStdTier] is null, "NoTier",[tblstdtier])
HAVING (((iif([tblStdTier] is null, "NoTier",[tblstdtier]))<>"0"));
0
 

Author Closing Comment

by:shogun5
Comment Utility
Yep! That worked! Thank you!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now