CODEWITHSUNDEEP
c#mysql
Michelle
Michelle

Reputation: 23

What is the right syntax on how to count the votes

This is the code that we used to COUNT the number of students who voted.

SELECT candidate,position, COUNT(studentNumber) AS 'Candidate Votes'
FROM dbvotingsystem.votes WHERE organization = 'iSITE' GROUP BY candidate
ORDER BY position;

The image is the sample of the data in the table.

enter image description hereom/CwvSt.png

Upvotes: 0

Views: 316

Answers (2)

Blank
Blank

Reputation: 12378

If the winner is the max Candidate Votes in each group, then Secretary's winner should be Many, check the following sql:

SELECT
    position,
    substring_index(group_concat(candidate order by `Candidate Votes` desc), ',', 1) winner,
    max(`Candidate Votes`) as `Candidate Votes`
FROM (
    SELECT candidate, position, COUNT(studentNumber) AS 'Candidate Votes'
    FROM dbvotingsystem.votes
    WHERE organization = 'iSITE'
    GROUP BY candidate
) votes
GROUP BY position

Result:

|    position | winner | Candidate Votes |
|-------------|--------|-----------------|
|   President | Audrey |               7 |
|   Secretary |   Many |               8 |
|   Treasurer |  Barry |              10 |
| V.President |   Juan |               9 |

DEMO in SQLFiddle.

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17665

Here's a way to do it using a variables to rank the results. Note the way the group by uses all the selected columns and that draws are catered for.

drop table if exists t;
create table t(id int auto_increment primary key, candidate varchar(1),position varchar(3));

insert into t (candidate,position) values
('a','p'),('b','p'),('a','p'),
('c','vp'),('c','vp'),('d','vp'),('d','vp');

select position,candidate, votes, rn as rank
from
(
select position,candidate, votes,
         if(s.position <> @p,@rn:=1,if(s.votes <> @v,@rn+1,@rn=1)) rn,
         @p:=position p,
         @v:=votes v
from
(
select position,candidate , count(*) as votes 
from t
group by position,candidate 
) s
,(select @rn:=0,@p:='',@v:='') r
order by position,candidate, votes desc
) t
where rn = 1

Upvotes: 0

Related Questions