按多列分组的 SQL 计数查询

Question

我有一个表格，其中包含名为“姓名”、“城市”和“职业”的三个填充列。我想在同一个表中创建一个新列，其中包含具有相同职业的人数。

"Name" | "City" | "Occupation"
------------------------------
Amy    | Berlin | Plumber
Bob    | Berlin | Plumber
Carol  | Berlin | Lawyer
David  | London | Plumber

我想要一个包含以下内容的表格:

"Name" | "City" | "Occupation" | "Number"
---------------------------------------
Amy    | Berlin | Plumber      | 2
Bob    | Berlin | Plumber      | 2
Carol  | Berlin | Lawyer       | 1
David  | London | Plumber      | 1

创建新列的 SQL 查询必须是什么样子的？我实际上想在数据库中创建一个我以后可以访问的新列。

Answer 1

select tbl.name, tbl.city, tbl.occupation,  x.number
from tbl
join
(
    select occupation, count(*) as number
    from tbl
    group by occupation
) as x on x.occupation = tbl.occupation