count multiple occurences of attribute values for each row in sql I have a mytable structured as follows and I would like to count occurences of values for attribute in each row:

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


With

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


I only get

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


But what I would like as a result is

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


How to achieve this?
select
  m1.id, 
  m1.attribute, 
  (select count(*) from mytable m2 where m2.attribute = m1.attribute) 
from
  mytable m1
;


Another version:

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)
;


A better version for databases with analytic/window functions:

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