A smart way to count null and non-null strings?

Question

I counted NULL and non-NULL columns with a subquery and some aggregate functions

CREATE TEMPORARY TABLE citizens(name text, country text,profession text,postalcode text); INSERT INTO citizens VALUES ('Fred', 'USA', 'Professor', NULL), ('Amy', 'USA', 'Professor', NULL), ('Ted', 'USA', 'Professor', 90210), ('Barb', 'USA', 'Lawyer', 10248), ('Wally', 'USA', 'Lawyer', NULL), ('Fred', 'Canada', 'Professor', 'S0H'), ('Charles', 'Canada', 'Professor', 'S4L'), ('Nancy', 'Canada', 'Lawyer', NULL), ('Linda', 'Canada', 'Professor', NULL), ('Steph', 'France', 'Lawyer', 75008 ), ('Arnold', 'France', 'Lawyer', 75008 ), ('Penny', 'France', 'Lawyer', 75008 ), ('Harry', 'France', 'Lawyer', NULL); SELECT country, profession, MAX(have_postalcode::int*num) AS num_have, MAX((1-have_postalcode::int)*num) AS num_not_have FROM ( SELECT country, profession, COUNT(*) AS num, (postalcode IS NOT NULL) AS have_postalcode FROM citizens GROUP BY country, profession, have_postalcode ) AS d GROUP BY country, profession 

with the result

 USA Professor 1 2 Canada Lawyer 0 1 USA Lawyer 1 1 France Lawyer 3 1 Canada Professor 2 1 

but it seems that there should be a slippery way (for example, it pains me that MAX used only to capture one non-trivial value). Does anyone have a cool idea?

Answer 1

 SELECT country, profession, COUNT(postalcode) AS num_have , (COUNT(*) - COUNT(postalcode)) AS num_not_have FROM citizens GROUP BY country, profession; 

http://sqlfiddle.com/#!1/17a9d/15

Answer 2

 SELECT country , profession , sum(case when postalcode is not null then 1 end) as num_have , sum(case when postalcode is null then 1 end) as num_not_have FROM citizens GROUP BY country , profession 

Answer 3

 SELECT Country, Profession, count(Country) as num_have, count(*) - count(PostalCode) as num_not_have FROM citizens GROUP BY Country, Profession