Election Polls Query-CodePudding

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

CodePudding user response：

This is the Final Answer

This is the Final Answer for one column that contains delimited CandidateID, So I convert it to multiple rows of VoterID and CandidateID (the good table design should have this table then just innerjoin voters and candidate table). Then I just use CTE and manipulate this with count and Group By.

CodePudding user response：

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