CODEWITHSUNDEEP
sqlpostgresql
Brian B
Brian B

Reputation: 1450

Slick way to count NULL and non-NULL rows?

I have been counting NULL and non-NULL columns with a subselect 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 like there ought to be a slicker way (for example it pains me that MAX is used just to grab the one nontrivial value). Does anybody have a cool idea?

Upvotes: 6

Views: 337

Answers (3)

Z .
Z .

Reputation: 12837

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

Upvotes: 4

closh
closh

Reputation: 313

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

Upvotes: 8

Andomar
Andomar

Reputation: 238296

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

Upvotes: 4

Related Questions