Complicated Select Query

neileisen · #1 (**permalink**) 02-04-08, 18:13

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...

Code:

SELECT state.ID, state.ShortName, state.Name, state.state_URL, state.Continent, count(votes.vote_id)  AS count_vote_id_1, votes.PartyID
FROM (state
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...

I have literally been trying for 2 days.

r937 · #2 (**permalink**) 02-04-08, 19:02

Code:

SELECT state.ID
     , state.ShortName
     , state.Name
     , state.state_URL
     , state.Continent
     , vdata.total_votes
     , vdata.PartyID_1_votes
     , vdata.PartyID_2_votes
  FROM state
LEFT OUTER
  JOIN ( SELECT Location
              , COUNT(*)   AS total_votes
              , SUM(CASE WHEN PartyID = 1
                         THEN 1 ELSE 0 END) 
                           AS PartyID_1_votes 
              , SUM(CASE WHEN PartyID = 2
                         THEN 1 ELSE 0 END) 
                           AS PartyID_2_votes 
           FROM votes 
         GROUP BY Location
       ) AS vdata
    ON vdata.Location = state.Continent

neileisen · #3 (**permalink**) 02-04-08, 19:12

Rudy,
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...
Amazing.
Thanks...Thanks, Thanks!

r937 · #4 (**permalink**) 02-04-08, 19:24

vdata is merely a table alias for the derived table produced by the subquery

thanks for the kind words

Internet Services

Web Development

Digital Marketing

Consumer Tech