基于分组数据的PostgreSQL计数数据
我有一个看起来像这样的数据库
users
email name state id
[email protected] Bill CA 1
Susan WY 2
[email protected] Jill CA 3
[email protected] Phil WY 4
你会发现,苏珊没有电子邮件。
我试图让每状态记录的计数,然后为每个国家非空的邮件的总数。
我能得到这样的状态的总数:
SELECT state, COUNT(*) as count FROM users GROUP BY state
这伟大工程。
然后我试图得到这样的电子邮件总数:
SELECT state, COUNT(*) as count, COUNT(SELECT * FROM users WHERE email IS NOT NULL) as email_count FROM users GROUP BY state
但是,返回解析语法错误。
我试图得到一个返回数据集是这样的:
[
{state: 'CA', count: 2, email_count: 2},
{state: 'WY', count: 2, email_count: 1}
]
回答如下:
尝试这个!
SELECT
state,
COUNT(*) AS total_count,
SUM(CASE WHEN email is not null then 1 ELSE 0 END) AS count_email_not_null
FROM users
GROUP BY state
它会给你你想要的输出。