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?

Improve this question

1 Answer 1

Reset to default
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 ;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.