CODEWITHSUNDEEP
sql
badhairdude
badhairdude

Reputation: 39

SQL Count and Duplicates

Assume a small town is holding elections, each citizen gets to vote as many times as they want, but their vote gets split the more times they vote, and they cannot do duplicate votes.

Voter   Vote
---------------
Jane    Jane
Howard  Jane
John    Howard
John    Howard
Jane    John
Sara    Howard

How would you in a SQL query determine who the winner was?

I'm a basic user so all I got is 

SELECT  
    vote, COUNT( vote) 
FROM 
    table1 
GROUP BY
    vote

This doesn't account for the duplicates or the splitting of the votes.

Help?

SQLFiddle

Upvotes: 1

Views: 467

Answers (5)

Cam
Cam

Reputation: 931

You may determine the appropriate weight of each voter's vote via subquery.

SELECT t1.vote AS 'Candidate', CAST(SUM(t2.`weight`) AS Decimal(6,2)) as 'Votes' 
FROM table1 AS t1 RIGHT JOIN (
 SELECT voter, 1.0 / CAST( COUNT(*) as Decimal) as `weight` 
 FROM table1 
 GROUP BY voter 
) AS t2 ON t1.voter = t2.voter 
GROUP BY t1.vote 
ORDER BY t1.vote;

http://sqlfiddle.com/#!9/9e569/48

Upvotes: 0

Naga Sai A
Naga Sai A

Reputation: 10975

You can use distinct voter and vote combination to remove duplicate votes

SELECT vote,COUNT(distinct voter) FROM table1 group by vote

Upvotes: 0

Turo
Turo

Reputation: 4914

Try this:

select vote, sum(1 / votes) as result from 
(SELECT voter, count(*) as votes from Table1 group by voter) votecount 
INNER JOIN Table1 on votecount.voter = Table1.voter
group by vote
order by result desc

this handles duplictaes in the way that one voter can give 2/3 and 1/3 votes

EDIT added order by

Upvotes: 0

Yosi Dahari
Yosi Dahari

Reputation: 6999

SELECT V.vote, SUM(W.weight) AS votes
FROM 
(
SELECT  voter, CAST(1 AS FLOAT) / COUNT(vote) AS weight
FROM table1 
group by voter
) W
JOIN
(
SELECT DISTINCT vote, voter
FROM table1 
) V ON W.voter = V.voter
GROUP BY V.vote

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You can use a dynamically created table 

 select vote, count(*) 
 from 
     (select distinct voter, vote 
      from table1) as t
 group by vote

Upvotes: 1

Related Questions