>我一直在计算带有子选择和一些聚合函数的 NULL 和非 NULL 列
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
结果
USA Professor 1 2
Canada Lawyer 0 1
USA Lawyer 1 1
France Lawyer 3 1
Canada Professor 2 1
但似乎应该有一种更光滑的方法(例如,MAX
只是用来抓取一个不平凡的价值,这让我很痛苦)。 有人有一个很酷的想法吗?
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
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
SELECT Country, Profession,
count(Country) as num_have, count(*) - count(PostalCode) as num_not_have
FROM citizens
GROUP BY Country, Profession