Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Red face Unanswered: Complicated Select Query

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    2
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    vdata is merely a table alias for the derived table produced by the subquery

    thanks for the kind words

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •