CODEWITHSUNDEEP
mysqlsql
Noman Khan
Noman Khan

Reputation: 3

Select the name from candidates table if he have highest entries in votes table

I have two table in database Table1: candidates

id | name
--------
1  | John
2  | Eva
3  | Siera

Table2: votes

| candidateid |
--------
|   1   |
|   1   |
|   1   |
|   1   |
|   2   |
|   2   |
|   3   |

Please someone help me my question is about to how can i select the name of person from candidates who have more entries in votes table.?

Upvotes: 0

Views: 744

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • Do a simple JOIN between the two tables.
  • Get total votes per candidate using COUNT function, over a grouping of candidate id.
  • Order the result by total votes in descending order, and using LIMIT 1 to get the details of the person with highest votes.

Try the following: 

SELECT c.id, 
       c.name, 
       COUNT(*) AS total_votes 
FROM candidates AS c 
JOIN votes AS v ON v.candidateid = c.id 
GROUP BY c.id 
ORDER BY total_votes DESC LIMIT 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

There is no voteId in your sample data. You seem to want:

SELECT CandidateId, count(*)
FROM votes
GROUP by CandidateId;

You can use a JOIN to bring in the name from the other table.

Upvotes: 0

Related Questions