Search code examples
sqldatabaseselectcountwindow-functions

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.

Solution

  • 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