你需要一个 LEFT 连接 votes 至 users 和条件聚合：

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

请看演示。
结果：

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

你应该使用 LEFT JOIN 在你的table之间 unique_yes 列应使用 DISTINCT 为了实现你的目标。

SELECT CASE WHEN USERS.CITY IS NOT NULL THEN USERS.CITY ELSE 'other city' END CITY,
         COUNT (
            DISTINCT CASE WHEN VOTES.VOTE = 'yes' THEN VOTES.VOTE ELSE NULL END)
            UNIQUE_YES,
         COUNT (CASE WHEN VOTES.VOTE = 'yes' THEN VOTES.VOTE ELSE NULL END)
            TOTAL_YES
    FROM VOTES LEFT JOIN USERS ON USERS.USER_ID = VOTES.USER_ID
GROUP BY CASE WHEN USERS.CITY IS NOT NULL THEN USERS.CITY ELSE 'other city' END

演示