sql query - how to count values in a row separately?

Question

I have a table that looks something like this:

id | firstperson | secondperson
1 | jane doe |
2 | bob smith | margie smith
3 | master shifu | madame shifu
4 | max maxwell | 

I'm trying to count all of the firstpersons + all of the secondpersons, if the secondpersons field isn't blank... Is there a way to do that?

Answer 1

select sum(1 + case when secondperson <> '' then 1 else 0 end)
from MyTable

Answer 2

SELECT SUM(CASE WHEN secondperson='' THEN 1 ELSE 2 END) FROM thetable;

Answer 3

I am not sure if this applies to Postgres or not, but in most databases, if we consider that there may be NULLS and that the question was really "if the secondperson field is not NULL", then you must explicitly include NULLS in the condition rather than the blank value.

Answer 4

SELECT SUM(CASE WHEN secondperson = '' OR secondperson IS NULL THEN 0 ELSE 2 END) FROM thetable;

Answer 5

Assuming that blank means NULL, then in postgres, like in other systems, the count(field) will count the field only if it is not null and

SELECT count(*)+count(secondperson) FROM thetable;

will give the wanted result. (SQL syntax respect NULLs better then custom values for missing data. Also, off topic, any fields that have numbers in their name are candidates for design revision)