Lalla Belle
asked on
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.
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.
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
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
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:
- Field to Summarize - in this case it's the CID field and you'll count the field
- 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
- 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...
ASKER
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
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
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:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, enjoy the coffee!
You are most welcome!!
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