I have a voting site and I have lots of tables, but there is one with region names, and one with votes.
The users that vote create a record in the vote table that includes vote_id (the primary key for that table) UserID (foreign key for the user table) ElectionID (foreign key for the election table) CandidateID (foreign key for the candidate table) PartyID (foreign key for the party table) and lastly Location (which is a the foreign key for the States table).
Seems simple... but in order to figure out who wins a state ...I need to subtract the numbers somehow in a select statement, and then produce a one-state-per-line recordset that includes every state.
Or... if I could for example determine that the democrat hat 5 votes in Massachusettes and the Republican had 3, and put those totals both on the same Massachussets line AS a new field name, like dem or rep, that could work too... but the best I was able to come up with was a query that produced a total for each party, so each state had 2 listings... except even then it was not right I dont think...
SELECT state.ID, state.ShortName, state.Name, state.state_URL, state.Continent, count(votes.vote_id) AS count_vote_id_1, votes.PartyID
LEFT JOIN votes ON votes.Location=state.Continent)
GROUP BY state.ID, state.ShortName, state.Name, state.state_URL, state.Continent, votes.PartyID
Is it even possible to put the totals for 2 different parties on one line?
Hopefully I am even explaining myself correctly...
JOIN ( SELECT Location
, COUNT(*) AS total_votes
, SUM(CASE WHEN PartyID = 1
THEN 1 ELSE 0 END)
, SUM(CASE WHEN PartyID = 2
THEN 1 ELSE 0 END)
GROUP BY Location
) AS vdata
ON vdata.Location = state.Continent
Thanks a MILLION. I went to your site and sent you a reply there as well. This is all new to me... I never even heard of vdata... but that's what I needed. And I am completely surprised that it works as-is...