By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

SQL - SUM in a sub-query in Access '03

P: 57
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
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.

May 30 '08 #1
Share this Question
Share on Google+
1 Reply

P: 57
Okay, I believe I found some code to make this work

(Select Sum([PopularVotes]) From [GovVotes] T Where T.Year = [CS].Year and T.State = [CS].State AND T.ElectionType = [CS].ElectionType) AS ElectionVotes.

Again, it's a web-snippet that I found so I might be doing this wrong, if anyone has a better way, I'll appreciate the more sound manner, but as of now, this is working for me.
May 30 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.