How do I count Nulls and not nulls at the same time?

I have a view in SQL server 2005, that I use as my reports data.

My records typically looks like this:

ProvinceID      PID      Province       CID      CName      CityID      CityName      Votes
20815      20815      Gauteng      208117      Pretoria      208117      Pretoria      321
20815      20815      Gauteng      208117      Pretoria      208117      Pretoria      386
20815      20815      Gauteng      208117      Pretoria      208117      Pretoria      311
20815      20815      Gauteng      208117      Pretoria      208117      Pretoria      323
20815      20815      Gauteng      208117      Pretoria      208117      Pretoria      321
20815      20815      Gauteng      208118      Boksburg      208118      Boksburg      218
20815      20815      Gauteng      208118      Boksburg      208118      Boksburg      320
20815      20815      Gauteng      208118      Boksburg      208118      Boksburg      430
20815      20815      Gauteng      208118      Boksburg      208118      Boksburg      340
20815      20815      Gauteng      208118      Boksburg      208118      Boksburg      350
10101      10101      KZN      101001      Durban      101001      Durban      40
NULL      10101      KZN      NULL      NULL      101002      Maritzburg      NULL
NULL      10101      KZN      NULL      NULL      101003      East London      NULL
NULL      10101      KZN      NULL      NULL      101004      Umhlanga      NULL
NULL      10101      KZN      NULL      NULL      101005      Salt Rock      NULL
NULL      10102      CPT      NULL      NULL      101006      Bellville      NULL

My desired outcome is:

Province      Total Cities with no votes              Total Cites
Gauteng      0                                    10
KZN      4                                     5
CPT      1                                     1

I group by PID, then I need to count all the Cities per Province where CID is NULL and where CID is not NULL (all the cities per province).

I can't seem to get it right!

Please advise.

Lalla BelleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
You wil have to count the nulls in a formula field.  Yu need 3 formula fields altogether.

In the group header:

WhilePrintingRecords;
Numbervar NullKnt:=0;
""

In the Detail line:

WhilePrintingRecords;
if isnull({table.CID}) then NullKnt:=NullKnt+1;
""
In the group Footer you display the count using a 3rd formula field.:

WhilePrintingRecords;
Numbervar NullKnt;
NullKnt
0
Kevin CrossChief Technology OfficerCommented:
To do this from the SQL side, you would just do something like this:
SELECT Province
, SUM(CASE WHEN CID IS NULL THEN 1 ELSE 0 END) AS [Total Cities with no votes]
, SUM(CASE WHEN CID IS NOT NULL THEN 1 ELSE 0 END) AS [Total Cities]
FROM ProvinceTable

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Sorry hit submit too quickly:
SELECT Province
, SUM(CASE WHEN CID IS NULL THEN 1 ELSE 0 END) AS [Total Cities with no votes]
, SUM(CASE WHEN CID IS NOT NULL THEN 1 ELSE 0 END) AS [Total Cities]
FROM ProvinceTable
GROUP BY Province

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Even easier, why not just use the Running Total field (assuming you're using CR7 or greater and it looks like you're using 2008)?  Each running total field has three parts:
  1. Field to Summarize - in this case it's the CID field and you'll count the field
  2. Evaluate - select "use a formula" and type in isnull({table.CID}) for the running total to count no votes and not isnull({table.CID}) for the running total to count cities with votes
  3. Reset - in your case, the running total needs to be reset on either the POD or Province fields - if you have an actual group for either of these fields, then use that, else use the field itself...
You will need to create two running totals, one for each type of count, but this is simpler than creating a manual running total that has 3 formulas for each count
0
Lalla BelleAuthor Commented:
mwvisa1:

When I run the query, the output is

CID      Total Cities with no votes      Total Cities
20809      6                                   0
20810      6                                   0
20811      0                                   50
20812      0                                   45
20813      0                                   45
20814      0                                   50
20815      0                                   55

It works fine when There are no nulls, but the 'Total cities' should equal the 'Total Cities with no votes' when they are null, now it says 0?

Thanks
0
Kevin CrossChief Technology OfficerCommented:
I got you.  I thought the you wanted the count of NULLs and count of NOT NULLs.

Am I understanding better now that you want count of NULLs and then count of ALL including nulls?

If so, think this should work:
SELECT Province
, SUM(CASE WHEN CID IS NULL THEN 1 ELSE 0 END) AS [Total Cities with no votes]
, SUM(*) AS [Total Cities]
FROM ProvinceTable
GROUP BY Province

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Arg, too quick to click SUBMIT button...too much coffee (usually I have the opposite problem).
SELECT Province
, SUM(CASE WHEN CID IS NULL THEN 1 ELSE 0 END) AS [Total Cities with no votes]
, COUNT(*) AS [Total Cities]
FROM ProvinceTable
GROUP BY Province

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lalla BelleAuthor Commented:
Thanks, enjoy the coffee!
0
Kevin CrossChief Technology OfficerCommented:
You are most welcome!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.