基于分组数据的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

它会给你你想要的输出。

基于分组数据的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

它会给你你想要的输出。