SQL Server Eliminate duplicate entries from my query results when using window function

sy5wg1nm 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(90)

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.

sql-server

来源：https://stackoverflow.com/questions/77262277/eliminate-duplicate-entries-from-my-query-results-when-using-window-function

1条答案

按热度按时间

20jt8wwn1#

Not sure why vote_date is in the output. Maybe you just want:

SELECT name, SUM(votes) as total_votes
FROM dbo.tableName
WHERE vote_date > '20230101'
GROUP BY name;

Or if you want the last date per person:

SELECT name, 
  MAX(vote_date) as last_vote,
  SUM(votes) as total_votes
FROM dbo.tableName
WHERE vote_date > '20230101'
GROUP BY name;

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.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Eliminate duplicate entries from my query results when using window function

1条答案

相关问题

热门标签

最新问答