SQL Aggregate Statement to Include Null Values and Values Not in a List

The result I am looking for is a table with each city, the number of unique yes votes, and the total yes votes. If there are yes votes in the votes table that don’t belong to a city in the users table, then it needs to return “other city” with the same aggregate stats. The result should look like…

CREATE TABLE users (
    user_id NUMERIC,
    city   STRING
);

insert into users (user_id, city)
values 
 (1, "nyc"),
 (2, "den"),
 (3, "nyc"),
 (4, "atl")
 
CREATE TABLE votes (
    sub_id,
    user_id,
    vote
);

insert into votes (sub_id, user_id, vote)
values
 (57, 1, "yes"),
 (57, 2, "yes"),
 (57, 5, "yes"),
 (57, 6, "no"),
 (58, 4, "no"),
 (58, 10, "no"),
 (58, 2, "yes"),
 (59, 12, "no");

Answer

You need a LEFT join of votes to users and conditional aggregation:

select coalesce(u.city, 'other city') city, 
       count(distinct case when vote = 'yes' then v.user_id end) unique_yes,
       sum(vote = 'yes') total_yes
from votes v left join users u
on v.user_id = u.user_id
group by u.city
order by u.city is null, u.city

See the demo.
Results:

| city       | unique_yes | total_yes |
| ---------- | ---------- | --------- |
| atl        | 0          | 0         |
| den        | 1          | 2         |
| nyc        | 1          | 1         |
| other city | 1          | 1         |