Solved

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

Posted on 2008-11-03
9
279 Views
Last Modified: 2010-04-21
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.

0
Comment
Question by:Lalla Belle
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22867406
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22867473
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22867476
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 22867498
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Lalla Belle
ID: 22868124
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22869205
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22869219
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
 

Author Closing Comment

by:Lalla Belle
ID: 31512695
Thanks, enjoy the coffee!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22870275
You are most welcome!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 101
How to query LOCK_ESCALATION 4 40
Exclude some records from totals 10 52
Can Unique column have more than one Null? 8 44
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 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

14 Experts available now in Live!

Get 1:1 Help Now