CODEWITHSUNDEEP
sqlpostgresqlcount
n00b0101
n00b0101

Reputation: 67

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

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?

Upvotes: 1

Views: 1795

Answers (5)

Unreason
Unreason

Reputation: 12704

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)

Upvotes: 0

Kuberchaun
Kuberchaun

Reputation: 30314

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

Upvotes: 0

JorgeLarre
JorgeLarre

Reputation: 103

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.

Upvotes: 0

Alex Martelli
Alex Martelli

Reputation: 881555

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

Upvotes: 5

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

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

Upvotes: 2

Related Questions