'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?

mysqlsql


Solution 1:[1]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 tinazmu

Related Questions