How to make a query for Big Brother nomination?

I have a table with three columns:

• Participant.
• First vote participant (two points).
• Second vote participant (one point).

CREATE TABLE `voting` (
  `participant` varchar(11) NOT NULL,
  `firstVote` varchar(11) NOT NULL,
  `secondVote` varchar(11) NOT NULL
)

I need a query that returns two columns: the participant and the number of points they received.

Example:

Query:

I take this line to thank in advance this beautiful community

>Solution :

Use a self LEFT join and conditional aggregation:

SELECT v1.participant,
       COALESCE(
         SUM(
           2 * (v1.participant = v2.firstVote) + 
           (v1.participant = v2.secondVote)
         ), 
         0
       ) AS votesReceived
FROM voting v1 LEFT JOIN voting v2
ON v1.participant IN (v2.firstVote, v2.secondVote)
GROUP BY v1.participant
ORDER BY votesReceived DESC;

Or:

SELECT v1.participant,
       SUM(
         CASE v1.participant  
           WHEN v2.firstVote THEN 2
           WHEN v2.secondVote THEN 1
           ELSE 0
         END
       ) AS votesReceived
FROM voting v1 LEFT JOIN voting v2
ON v1.participant IN (v2.firstVote, v2.secondVote)
GROUP BY v1.participant
ORDER BY votesReceived DESC;

See the demo.