I hope my question here is clear. I have a table GovVotes that contains the following fields: Year/State/ElectionType/Candidate/Party/PopularVotes.
Via net-searching, I found a code for a query I wanted to perform, basically take all those fields and display what Rank each candidate was in each particular race, based on Year/State/ElectionType. I then added a query field called Result based on the Rank created field.
The SQL for this query looks like this:
SELECT CS.Year, CS.State, CS.ElectionType , CS.Candidate, CS.Party, CS.PopularVotes , (SELECT COUNT(*) + 1 FROM GovVotes AS C WHERE (C.PopularVotes > [CS.PopularVotes] AND C.State = CS.State AND C.Year = CS.Year AND C.ElectionType = CS.ElectionType )) AS Rank, IIf([Rank]=1,"Win","Loss" ) AS Result
FROM GovVotes AS CS
ORDER BY CS.Year, CS.State, CS.ElectionType , CS.PopularVotes DESC;
The CS & C are the designations the snippet of code I got from the internet contained, they hold no meaning to me.
What I want to try to add is a sum of all votes cast in each election. For instance, my data set at this point looks like this:
Year State ElectionType Candidate Party PopularVotes Rank Result
2006 Maryland General O'Malley, Martin Democrat 942279 1 Win
2006 Maryland General Ehrlich, Robert L. Republican 825464 2 Loss
2006 Maryland General Boyd, Ed Green 15551 3 Loss
2006 Maryland General Driscoll, Christopher A. Populist 3481 4 Loss
2006 Maryland General Write-In 1541 5 Loss
2008 New York General TestData Test 10000 1 Win
I want to add a column called ElectionVotes that would display the total votes cast in each Year/State/ElectionType. And I have zero clue how the SQL would work for this. I'm trying to learn SQL, but I'm easily stumped. Any help would be appreciated. If it's necessary to re-do the existing SQL, that's more than welcome at this point as well.
Joseph
Via net-searching, I found a code for a query I wanted to perform, basically take all those fields and display what Rank each candidate was in each particular race, based on Year/State/ElectionType. I then added a query field called Result based on the Rank created field.
The SQL for this query looks like this:
SELECT CS.Year, CS.State, CS.ElectionType , CS.Candidate, CS.Party, CS.PopularVotes , (SELECT COUNT(*) + 1 FROM GovVotes AS C WHERE (C.PopularVotes > [CS.PopularVotes] AND C.State = CS.State AND C.Year = CS.Year AND C.ElectionType = CS.ElectionType )) AS Rank, IIf([Rank]=1,"Win","Loss" ) AS Result
FROM GovVotes AS CS
ORDER BY CS.Year, CS.State, CS.ElectionType , CS.PopularVotes DESC;
The CS & C are the designations the snippet of code I got from the internet contained, they hold no meaning to me.
What I want to try to add is a sum of all votes cast in each election. For instance, my data set at this point looks like this:
Year State ElectionType Candidate Party PopularVotes Rank Result
2006 Maryland General O'Malley, Martin Democrat 942279 1 Win
2006 Maryland General Ehrlich, Robert L. Republican 825464 2 Loss
2006 Maryland General Boyd, Ed Green 15551 3 Loss
2006 Maryland General Driscoll, Christopher A. Populist 3481 4 Loss
2006 Maryland General Write-In 1541 5 Loss
2008 New York General TestData Test 10000 1 Win
I want to add a column called ElectionVotes that would display the total votes cast in each Year/State/ElectionType. And I have zero clue how the SQL would work for this. I'm trying to learn SQL, but I'm easily stumped. Any help would be appreciated. If it's necessary to re-do the existing SQL, that's more than welcome at this point as well.
Joseph
Comment