In PostgreSQL, how to query for a computed results by the grouping of a field?

In my PostgreSQL database I have an invitations table like so:

Invitations Table:

id, created_at, source, completed_at

Fields Details

id: integer
created_at: timestamp
source: varchar
completed_at: timestamp

I’d like to query the Invitations table and group by unique sources. And then per source, include the total # of records and the total of those completed. Completed being where complated_at is NOT NULL.

Desired Results:

columns: source, total, total_completed

source_a | 100 | 50
source_b | 200 | 33
source_c | 301 | 3

Suggestions?

Answer

You can do aggregation like so:

select source,  count(*) total, count(completed_at) total_completed
from invitations
group by source

count(*) counts all records in the group, while count(completed_at) counts only records whose completed_at is not null.