SQL query for getting the winner in election help!!

[eidecedie17]

guys help naman pano ko makukuha yung winner dito for every position, ididisplay ko sya ng ganito

candidateName
PositionName
TotalVote
order by positionID



By the way yang table po na yan ay VIEW statement pong ginawa ko para makita ang total vote for every candidate

 

[lokijuy]

Sa Pagkuha ng Highest vote gumamit ka na MAX() at sa pag sort naman ng position gumamit ka ng GROUP BY.

Sample:
SELECT `Name`,`Position`,MAX(`Vote`) FROM `tablevote` GROUP BY `Position`

 

[eidecedie17]

Sa Pagkuha ng Highest vote gumamit ka na MAX() at sa pag sort naman ng position gumamit ka ng GROUP BY.

Sample:
SELECT `Name`,`Position`,MAX(`Vote`) FROM `tablevote` GROUP BY `Position`

actuallly kanina ko pa sya ginagawa sir, nagdidisplay sya kapag wala yung CandidateName. Kaso lang di pwedeng wala yun e.
Hindi ko naman maggroup by ang candidate name lumalabas lahat ng candidate. Kaso ayan ang error o

 

[lokijuy]

actuallly kanina ko pa sya ginagawa sir, nagdidisplay sya kapag wala yung CandidateName. Kaso lang di pwedeng wala yun e.
Hindi ko naman maggroup by ang candidate name lumalabas lahat ng candidate. Kaso ayan ang error o

Mali ang sql statement na to: SELECT `Name`,`Position`,MAX(`Vote`) FROM `tablevote` GROUP BY `Position`
di ko napansin phpmyadmin kasi gamit ko sa pagtest.
Kukunin nya ang highest vote kada position pero kukunin nya lagi ang 1st candidate ng bawat Position. which is WRONG


Ito ang Tamang Statement
Kukunin nya ang highest vote kada position and kukunin nya ang bawat candidate na match sa highest vote kada position.
Try This sir:
SELECT `Name`, `Position`,`Vote` FROM tablevote
WHERE `Vote` in (SELECT MAX(`Vote`) FROM tablevote GROUP BY `Position`)
ORDER BY `Position`

 

[eidecedie17]

Try This sir:
SELECT `Name`, `Position`,`Vote` FROM tablevote
WHERE `Vote` in (SELECT MAX(`Vote`) FROM tablevote GROUP BY `Position`)
ORDER BY `Position`

wooooooooooooohooooooooooooooooooooooooo
inedit ko lang ng konti sir para sa pagsort ko

SELECT canName, positionName,TotalVote FROM [COUNT VOTE]
WHERE TotalVote in (SELECT MAX(TotalVote) FROM [COUNT VOTE] GROUP BY positionName, positionID)
ORDER BY positionID

maraming maraming salamat

 

[lokijuy]

No Problem sir.

 

[jess1993]

Mali ang sql statement na to: SELECT `Name`,`Position`,MAX(`Vote`) FROM `tablevote` GROUP BY `Position`
di ko napansin phpmyadmin kasi gamit ko sa pagtest.
Kukunin nya ang highest vote kada position pero kukunin nya lagi ang 1st candidate ng bawat Position. which is WRONG


Ito ang Tamang Statement
Kukunin nya ang highest vote kada position and kukunin nya ang bawat candidate na match sa highest vote kada position.
Try This sir:
SELECT `Name`, `Position`,`Vote` FROM tablevote
WHERE `Vote` in (SELECT MAX(`Vote`) FROM tablevote GROUP BY `Position`)
ORDER BY `Position`

hi sir, parang mali pa rin po result niya kapag nagpareparehas na votecount niya

try this:

SELECT canName, positionName,MAX(TotalVote) FROM tableVote GROUP BY positionID ORDER BY TotalVote DESC
gamit to kukunin niya max total vote and we order it by total vote para yung pinakamataas muna ilagay niya sa list pero kapag tie sa position isa lang lalabas

or you can use LEFT JOIN

SELECT A.CANDITATE_NAME,A.POSITION_NAME,A.TOTAL_VOTE FROM TABLE_VOTE A
LEFT JOIN (
SELECT POSITION_ID,MAX(TOTAL_VOTE) AS TOTAL_VOTE FROM TABLE_VOTE GROUP BY POSITION_ID
) B
ON A.POSITION_ID=B.POSITION_ID
WHERE A.TOTAL_VOTE=B.TOTAL_VOTE

using this query, kapag may nag tie sa POSITION parehas sila lalabas kung highest vote yung total vote nila

 

[eidecedie17]

hi sir, parang mali pa rin po result niya kapag nagpareparehas na votecount niya

try this:

SELECT canName, positionName,MAX(TotalVote) FROM tableVote GROUP BY positionID ORDER BY TotalVote DESC
gamit to kukunin niya max total vote and we order it by total vote para yung pinakamataas muna ilagay niya sa list pero kapag tie sa position isa lang lalabas

or you can use LEFT JOIN

SELECT A.CANDITATE_NAME,A.POSITION_NAME,A.TOTAL_VOTE FROM TABLE_VOTE A
LEFT JOIN (
SELECT POSITION_ID,MAX(TOTAL_VOTE) AS TOTAL_VOTE FROM TABLE_VOTE GROUP BY POSITION_ID
) B
ON A.POSITION_ID=B.POSITION_ID
WHERE A.TOTAL_VOTE=B.TOTAL_VOTE

using this query, kapag may nag tie sa POSITION parehas sila lalabas kung highest vote yung total vote nila

maraming salamat sir, gumana din, eto yung ginawa ko


SELECT A.canName,A.positionName,A.TotalVote FROM [COUNT VOTE] A
LEFT JOIN (
SELECT positionID,MAX(TotalVote) AS TOTAL_VOTE FROM [COUNT VOTE] GROUP BY positionID
) B
ON A.positionID=B.positionID
WHERE A.TotalVote=B.TOTAL_VOTE
ORDER BY A.positionID

 

[kagidka]

SORT MO LNG yung Score to DESC yun lng... then filter the highest score of CandidatesName