Counting null vs non-null values

emily

Okay, this problem is driving me crazy! There's probably a really simple solution, but whatever it is, I can't see it! 🙂

Basically, I have a date field called "deletedate" in my table. Based on certain criteria (which is irrelevant here), I want to return a count of how many rows have a null value for deletedate, and how many rows have a non-null value. The non-null part is easy - just count(deletedate). But how do I count how many rows have null values?

I think I've achieved this in MySQL:
SELECT count(IF(isnull(p.deletedate),1,null)) as deletedatenull,
count(p.deletedate) as deletedatenotnull
FROM member
WHERE ... etc.

A little messy, but it switches the null and non-null values around, and then does the count. The problem is that I now need to achieve the same in PostgreSQL (ie. it needs to be rewritten without the IF and ISNULL). Does anyone have any ideas on how I can achieve this? Any help would be very much appreciated 🙂

Emily

zhabala

You can do that with the nested SELECT statemets like:

SELECT ( SELECT count() FROM members WHERE deletedate IS NULL AND ...the rest of where clause... ) AS nulls,
( SELECT count() FROM members WHERE deletedate IS NOT NULL AND ...the rest of where clause... ) AS notnulls;

It is not so nice, but it works in PostgreSQL.

Zdenek

Sxooter

Originally posted by emily
Okay, this problem is driving me crazy! There's probably a really simple solution, but whatever it is, I can't see it! 🙂

Basically, I have a date field called "deletedate" in my table. Based on certain criteria (which is irrelevant here), I want to return a count of how many rows have a null value for deletedate, and how many rows have a non-null value. The non-null part is easy - just count(deletedate). But how do I count how many rows have null values?

Try this:

select count(*) from table where field is NULL

select count(*) from table where field is NOT NULL

emily

Thanks for your help! I tried sub-queries, but they kept on returning too much data... think it was the way I was writing them, they didn't seem to work with all the joins and having clauses I was using in the query.

I did come up with a case statement that seems to do the job though... it measures the length of deletedate; if it's 22, then the field contains a date, otherwise that field must be null.

SELECT count(CASE length(p.deletedate) WHEN 22 THEN null ELSE 1 END) as deletedatenull,
count(p.deletedate) as deletedatenotnull
FROM member
WHERE ... etc.

It's pretty slow, but I only needed to run it once, so it doesn't matter... the main thing is that it works 🙂

Sxooter

Trust me guys, I just tested my where x is null code and it works a charm. Man, what contortions you have to go through in MySQL for some things. Of course, there are some things that are lines of code in Postgresql that are one simple command in MySQL as well.

zhabala

Sure Sxooter, solution which I proposed is combination of two selects you mentioned into one SQL statement, because PostgreSQL alowes that.

Everytime you need to count NULL values you have to (should) use IS NOT NULL or IS NULL clause.

Zdenek