計算sql中每一行屬性值的多次出現

我的mytable結構如下，我想計算attribute每一行中值的出現次數：

id | attribute
--------------
1  | spam
2  | egg
3  | spam

和

SELECT id, attribute, COUNT(attribute) FROM mytable GROUP BY attribute

我只得到

id | attribute | count
----------------------
1  | spam      | 2 
2  | egg       | 1

但我想要的結果是

id | attribute | count
----------------------
1  | spam      | 2 
2  | egg       | 1
3  | spam      | 2

如何做到這一點？

select
 m1.id, 
 m1.attribute, 
 (select count(*) from mytable m2 where m2.attribute = m1.attribute) 
from
 mytable m1
;

另一個版本：

select
 m1.id,
 m1.attribute,
 m2.c
from
 mytable m1
 join (SELECT attribute, COUNT(attribute) as c FROM mytable GROUP BY attribute) m2
 on (m1.attribute = m2.attribute)
;

具有分析/視窗功能的數據庫的更好版本：

select
 m1.id,
 m1.attribute,
 count(*) over (partition by m1.attribute)
from
 mytable m1
;

引用自：https://dba.stackexchange.com/questions/176860