5

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.