'Election Polls Query

I have SQL tables below.

Candidates
ID Name Position
1 Pacquio President
2 Leni    President
3 Herbert Senator
4 Tulfo   Senator
5 Marcoleta Senator

Voters
ID  Name Candidates
1  Noel 1,3,4
2 Mar   2,4,5
3 Dan.  2,4,3

The result should be below

Results
Position Name Votes
President Leni 2
President Pacquio 1
Senator Tulfo 2
Senator Herbert 1
Senator Marcoleta 1

What should be my SQL Query

Thanks very much Noel

c++sharpsql


Solution 1:[1]

enter image description here

;WITH CastedVotes(VoterID,CandidateID) as --using CTE
(
SELECT ID, value  
FROM Voters  
CROSS APPLY STRING_SPLIT(Candidates, ',')
)
SELECT DISTINCT C.NAME
, Position
, COUNT (C.ID) OVER (PARTITION BY C.ID) AS VotesCount
FROM CastedVotes CV 
INNER JOIN Voters V ON VoterID=V.ID
INNER JOIN Candidates C ON CandidateID=C.ID
ORDER BY Position, VotesCount DESC

Here is the answer, I want the field position first. Any Idea maybe group by

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Enigmativity

Related Questions