CODEWITHSUNDEEP
sqldatabaseselectcountwindow-functions
R.vW
R.vW

Reputation: 177

Counting the amount of object with the same value in another column

I want to count the amount of an occurrance the reattacht that the row back and couldn't find any good way to do it.

So 1 table would look like 

 id | value

 1. a
 2. a
 3. b
 4. a
 5. b
 6. b
 7. c
 8. c
 9. a

which I would like to result in:

 id | value | count

 1. a,  4
 2. a,  4
 3. b,  3
 4. a,  4
 5. b,  3
 6. b,  3
 7. c,  2
 8. c,  2
 9. a,  4

I can only find answers with group by so any help is appreciated. This should also be matched to another table so if the result is joinable that would be helpful as well.

Upvotes: 0

Views: 347

Answers (2)

GMB
GMB

Reputation: 222622

If your RDBMS support window functions, no need to join: you can just do a window count:

select t.*, count(*) over(partition by value) cnt from mytable t

Upvotes: 3

juergen d
juergen d

Reputation: 204894

select t.id, t.value, tmp.cnt
from your_table t
join
(
  select value, count(*) as cnt
  from your_table
  group by value
) tmp on tmp.value = t.value

Upvotes: 2

Related Questions