Given following query:
select *
from (
select name, vote_date
, sum(votes) over (partition by name) as total_votes
from table
) as subq
where vote_date > '2023-01-01'
and total_votes > 4
This is a sample output:
vote_date | name | total_votes |
---|---|---|
2023-01-08 | Alex | 6 |
2023-07-14 | Bob | 8 |
2023-07-19 | Bob | 8 |
2023-04-18 | Carol | 9 |
2023-02-03 | Carol | 9 |
2023-07-20 | Diana | 10 |
2023-05-22 | Diana | 10 |
2023-08-21 | Rizlf | 10 |
2023-09-27 | Rizlf | 10 |
I want to eliminate duplicates form my results and only show one entry for each name with the total_votes. The data has the same person voting across multiple days
I have tried to use a GROUP BY name but I get an error. Column 'subq.vote_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I have also tried removing the vote_date from the subquery but I get another error I think because I need it for the WHERE clause.
1条答案
按热度按时间20jt8wwn1#
Not sure why vote_date is in the output. Maybe you just want:
Or if you want the last date per person:
But having all the vote dates in the output and also trying to group by it or eliminate duplicates that are only different in the column can’t all be done at the same time.