node.js - PostgreSQL 根据分组数据统计数据

Question

我有一个看起来像这样的数据库

users
email             name    state       id
bill@domain.com   Bill    CA           1
                  Susan   WY           2
jill@domain.com   Jill    CA           3
phil@domain.com   Phil    WY           4

您会注意到 Susan 没有电子邮件。

我正在尝试获取每个州的记录数，然后是每个州的非空电子邮件总数。

我能够像这样获得状态的总数:

SELECT 状态，COUNT(*) as count FROM users GROUP BY state

效果很好。

然后我尝试获取这样的电子邮件总数:

SELECT state, 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}
]

Answer 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

它将为您提供预期的输出。