How to add a column to table results, with the number of repeats of a certain value on a row?

For example, we have a table:

| id | field | value |
|----|-------|-------|
| 1  | X     | Y     |
| 1  | V     | Y     |
| 1  | Z     | W     |
| 1  | Z     | T     |

and I want to have a following output:

| id | field | value | field_occurencies |
|----|-------|-------|-------------------|
| 1  | X     | Y     | 1 |
| 1  | V     | Y     | 1 |
| 1  | Z     | W     | 2 |
| 1  | Z     | T     | 2 |

Is there any way to do this?

Answer

Windowing functions are for that very purpose:

COUNT(*) over (partition by field) as field_occurence

We can’t tell if your particular database product supports window functions (you didn’t tell us)