Reputation: 1450
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
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
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
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