SQL里怎么在新的列中显示每个种类的总数

Question

数据如图，一共有4个Documentary和2个Horror，怎么得到红圈内的列。

drop table if exists  film_test; 
CREATE TABLE film_test  (
   film_id  smallint(5)  NOT NULL,
   name  varchar(25) NOT NULL);
INSERT INTO film_test VALUES(1,'Documentary');
INSERT INTO film_test VALUES(2,'Horror');
INSERT INTO film_test VALUES(3,'Documentary');
INSERT INTO film_test VALUES(4,'Horror');
INSERT INTO film_test VALUES(5,'Documentary');
INSERT INTO film_test VALUES(6,'Documentary');

Answer 1

select
file_id,
name,
(select count(*) from film_test b where b.name=a.name)
from film_test a

用子查询吧。