Adding Race Candidates to my current statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding Race Candidates to my current statement

Author

Topic

snots34
Starting Member

12 Posts

Posted - 2014-02-13 : 20:10:57

Hi,

This returns 2 candidates with their vote totals and precinct percents and what race they are associated with. Now it has come to my attention that there are some races that have 3 up to 5 candidates associated with a single race, so this does not work anymore. I mean...it works, but it only returns the first 2 candidates of a race that has 3 or more candidates in it. Can this be done? I can pull the extra candidates from the [CANDIDATE] table, but not sure how to get them in the same row set or within the same race.

select  rc.[race number] AS RaceNumber,
    max(case when seqnum = 1 then title1 end) as title1,
    max(case when seqnum = 1 then [precinct percent] end) as PrecintPercent,
    max(case when seqnum = 1 then [candidate num] end) as Winner,
    max(case when seqnum = 1 then Votes end) as WinningVotes,
    max(case when seqnum = 1 then party end) as WinningParty,
    max(case when seqnum = 1 then leader end) as Winner1,
    max(case when seqnum = 1 then CAST(winner AS tinyint) end) as WinnerSelected,
    max(case when seqnum = 1 then [leader percent] end) as WinnerPercent,
    max(case when seqnum = 2 then [candidate num] end) as Loser,
    max(case when seqnum = 2 then Votes end) as LosingVotes,
    max(case when seqnum = 2 then party end) as LosingParty,
    max(case when seqnum = 2 then leader2 end) as Loser2,
    max(case when seqnum = 2 then [leader2 percent] end) as LoserPercent,
    max(case when seqnum = 2 then CAST(winner AS tinyint) end) as LoserSelected


from 
(
select  
        r.title1,
        r.[precinct percent],
        rc.[race number],
        rc.[candidate num],
        rc.[Votes],
        rc.[winner],
        c.[party],
        r.[leader],
        r.[leader percent],
        r.[leader2],
        r.[leader2 percent],

            row_number() over (partition by rc.[race number] order by votes desc) as seqnum
    from    dbo.[RACE CANDIDATES] rc
    inner join dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
    inner join dbo.[RACE] r
     on rc.[race number] = r.[race number] 

) rc
group by rc.[race number]

Please let me know if you have any suggestions. Any would be helpful. Thank You very much for taking your time to look at this.

snots34
Starting Member

12 Posts

Posted - 2014-02-13 : 20:27:22

Ok, leader and leader 2 are columns. Leader is the current candidate that has the highest votes, leader 2 is the candidate losing.

I have found the columns first name and last name, have the names I need. I just do not know how to order them in my above statement so each candidate (fist name and last name) follows their vote totals. Such as, candidate1 as the winner, candidate2 as winner2, candidate3 as losing and candidate4 losingmore. I know I am not making any sense, but I just want all the candidates in a single row and a single race. that's what I was able to accomplish with the above statement, but there are more than 2 candidates in a few of the races. I am working on it...

snots34
Starting Member

12 Posts

Posted - 2014-02-13 : 21:41:15

I got it...


select  rc.[race number] AS RaceNumber,
	max(case when seqnum = 1 then title1 end) as title1,
	max(case when seqnum = 1 then [precinct percent] end) as PrecintPercent,
    max(case when seqnum = 1 then [candidate num] end) as Winner,
    max(case when seqnum = 1 then Votes end) as WinningVotes,
    max(case when seqnum = 1 then party end) as WinningParty,
    max(case when seqnum = 1 then leader end) as Winner1,
    max(case when seqnum = 1 then CAST(winner AS tinyint) end) as WinnerSelected,
    max(case when seqnum = 1 then [leader percent] end) as WinnerPercent,
    max(case when seqnum = 2 then [candidate num] end) as Loser,
    max(case when seqnum = 2 then Votes end) as LosingVotes,
    max(case when seqnum = 2 then party end) as LosingParty,
    max(case when seqnum = 2 then leader2 end) as Loser2,
    max(case when seqnum = 2 then [leader2 percent] end) as LoserPercent,
    max(case when seqnum = 2 then CAST(winner AS tinyint) end) as LoserSelected,
    max(case when seqnum = 3 then [candidate num] end) as Winner3,
    max(case when seqnum = 3 then Votes end) as Winner3Votes,
    max(case when seqnum = 3 then party end) as Winner3Party,
    max(case when seqnum = 3 then [first name]end) as Winner3FirstName,
    max(case when seqnum = 3 then [last name]end) as Winner3LastName,
    max(case when seqnum = 3 then CAST(winner AS tinyint) end) as Winner3Selected,
    max(case when seqnum = 4 then [candidate num] end) as Loser4,
    max(case when seqnum = 4 then Votes end) as Loser4Votes,
    max(case when seqnum = 4 then party end) as Loser4Party,
    max(case when seqnum = 4 then [first name]end) as Loser4FirstName,
    max(case when seqnum = 4 then [last name]end) as Loser4LastName,
    max(case when seqnum = 4 then CAST(winner AS tinyint) end) as Loser4Selected
    

from 
(
select  
		r.title1,
		r.[precinct percent],
		rc.[race number],
        rc.[candidate num],
        rc.[Votes],
        rc.[winner],
        c.[party],
        r.[leader],
        r.[leader percent],
        r.[leader2],
        r.[leader2 percent],
        c.[first name],
        c.[last name],
        
            row_number() over (partition by rc.[race number] order by votes desc) as seqnum
    from    dbo.[RACE CANDIDATES] rc
    inner join dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
    inner join dbo.[RACE] r
     on rc.[race number] = r.[race number] 
          
) rc
group by rc.[race number]

Ooops, I need to have the First and Last names in the same column. I'll get it....i think.

Thanks for looking...and perhaps this lame question will help some others out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources