Having a table:
| id | candidateId |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 5 |
I'm trying to use the following query:
SELECT DISTINCT candidateId, COUNT(candidateId)
...
which returns only one row:
| candidateId | count(v.candidateId) |
|---|---|
| 2 | 5 |
while I want to compute the total count of candidates for each record as follows:
| candidateId | count(v.candidateId) |
|---|---|
| 2 | 5 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
EDIT: Trying the solution proposed in the comments:
select candidateId, count(candidateId) from Vote group by candidateId
I get the following return, which IS NOT the desired outcome:
| candidateId | count(candidateId) |
|---|---|
| 2 | 2 |
| 4 | 1 |
| 3 | 1 |
| 5 | 1 |
CodePudding user response：
It looks like you don't need aggregation but want to add the total count of elements to each row.
If that's the case, you need to use the corresponding COUNT window function. It will compute the total and assign it to every record of your table.
SELECT candidateId, COUNT(candidateId) OVER() AS cnt
FROM tab