Skip to content

Instantly share code, notes, and snippets.

@dsartori

dsartori/VoteShareByRiding.sql

Created April 9, 2022 13:30
Show Gist options
  • Save dsartori/c516dbd98f9b7d8433d8d577cc3f25d0 to your computer and use it in GitHub Desktop.
Save dsartori/c516dbd98f9b7d8433d8d577cc3f25d0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. dsartori created this gist Apr 9, 2022.
    36 changes: 36 additions & 0 deletions VoteShareByRiding.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,36 @@
    DROP TABLE IF EXISTS #VotesCast

    SELECT
    e.ECNumber,
    e.EcNameEn,
    SUM(CASE WHEN e.electionyear = 2015 THEN pr.votes ELSE 0 END) AS Votes2015,
    SUM(CASE WHEN e.electionyear = 2019 THEN pr.votes ELSE 0 END) AS Votes2019,
    SUM(CASE WHEN e.electionyear = 2021 THEN pr.votes ELSE 0 END) AS Votes2021
    INTO #VotesCast
    FROM pollresult pr
    INNER JOIN poll p ON pr.pollid = p.pollid
    INNER JOIN ed e ON e.edid = p.edid
    GROUP BY e.EcNumber,e.EcNameEn
    --select * from #VotesCast

    SELECT
    pro.Province,
    ed.ECNumber,
    ed.ECNameEn,
    pa.PartyNameEn,
    CASE WHEN MAX(v.Votes2015) > 0 THEN SUM(CASE WHEN ed.ElectionYear = 2015 THEN CAST(Votes AS DECIMAL(10,6)) ELSE 0 END) / MAX(v.Votes2015) ELSE 0 END AS 'Vote Share 2015' ,
    CASE WHEN MAX(v.Votes2019) > 0 THEN SUM(CASE WHEN ed.ElectionYear = 2019 THEN CAST(Votes AS DECIMAL(10,6)) ELSE 0 END) / MAX(v.Votes2019) ELSE 0 END AS 'Vote Share 2019',
    CASE WHEN MAX(v.Votes2021) > 0 THEN SUM(CASE WHEN ed.ElectionYear = 2021 THEN CAST(Votes AS DECIMAL(10,6)) ELSE 0 END) / MAX(v.Votes2021) ELSE 0 END AS 'Vote Share 2021'
    FROM PollResult pr
    INNER JOIN poll p ON pr.pollID = p.pollID
    INNER JOIN candidate c ON c.CandidateID = pr.candidateID
    INNER JOIN party pa ON c.partyID = pa.partyID
    INNER JOIN ed ON ed.EDID = p.EDID
    INNER JOIN ED_province() pro ON ED.EDID = pro.EDID
    INNER JOIN #VotesCast v ON ed.ECNameEn = v.ECNameEn
    WHERE Pa.PartyNameEn IN ('Conservative','Liberal','NDP-New Democratic Party','Bloc Québécois','Green Party')
    GROUP BY pro.province,ed.ECNumber,ed.ECNameEn,pa.PartyNameEn
    ORDER BY Province,ECNameEn, PartyNameEn