CODEWITHSUNDEEP
node.jspostgresql
Jordash
Jordash

Reputation: 3093

PostgreSQL count data based on grouped data

I have a database that looks like this

users
email             name    state       id
[email protected]   Bill    CA           1
                  Susan   WY           2
[email protected]   Jill    CA           3
[email protected]   Phil    WY           4

You'll notice that Susan does not have an email.

I'm trying to get a count of records per state, then a total number of non null emails for each state.

I was able to get the total count of states like this:

SELECT state, COUNT(*) as count FROM users GROUP BY state

That works great.

Then I tried getting the total number of emails like this:

SELECT state, COUNT(*) as count, COUNT(SELECT * FROM users WHERE email IS NOT NULL) as email_count FROM users GROUP BY state

But that returned a parse syntax error.

I'm trying to get a return dataset like this:

[
  {state: 'CA', count: 2, email_count: 2},
  {state: 'WY', count: 2, email_count: 1}
]

Upvotes: 1

Views: 49

Answers (1)

Rupert
Rupert

Reputation: 150

Try this!

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

It will give you your intended output.

Upvotes: 1

Related Questions