XML won't output Divide by zero encountered in SQL Query

Question

I have a Query that selects Election data. Of course, when you start an election, there are 0 votes input for all candidates. But gradually, votes get entered. In order to get the percentage to output from the results, I took, number of votes multiplied by 100 then divided by total votes and I got my percentage.

Problem- once my query runs for the xml path, there is an error written in the top line of the xml document, "Divide by Zero error encountered". In order to bypass this, I just commented out the divide lines, but I would not like to do that. I figured, if I waited until there was a race that had votes in each field, I would then un-comment that line of code and let it output the percentage of only that race....until ALL races had at least some votes within them.

Here is my sql code:

:XML ON
select 
    max(case when seqnum = 1 then title1 end) as title1,
    max(case when seqnum = 1 then [precinct percent] end) as PrecinctPercent,
    max(case when seqnum = 1 then [candidate num] end) as WinnerNum,
    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 Winner,
    max(case when seqnum = 1 then CAST(winner AS tinyint) end) as WinnerSelected,
    max(case when seqnum = 1 then ((Votes * 100) / ([total vote])) end) as WinnerPercent,
    max(case when seqnum = 2 then [candidate num] end) as LoserNum,
    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 Loser,
    max(case when seqnum = 2 then ((Votes * 100) / ([total vote])) end) as LoserPercent,
    max(case when seqnum = 2 then CAST(winner AS tinyint) end) as LoserSelected,
    max(case when seqnum = 3 then title1 end) as title1,
    max(case when seqnum = 3 then [precinct percent] end) as PrecinctPercent,
    max(case when seqnum = 3 then [candidate num] end) as WinnerNum,
    max(case when seqnum = 3 then Votes end) as WinningVotes,
    max(case when seqnum = 3 then ((Votes * 100) / ([total vote])) end) as WinnerPercent,
    max(case when seqnum = 3 then party end) as WinningParty,
    max(case when seqnum = 3 then [first name]+ ' ' + [last name] end) as Winner,
    max(case when seqnum = 3 then CAST(winner AS tinyint) end) as WinnerSelected,
    max(case when seqnum = 4 then [precinct percent] end) as PrecinctPercent,
    max(case when seqnum = 4 then [candidate num] end) as LoserNum,
    max(case when seqnum = 4 then Votes end) as LosingVotes,
    max(case when seqnum = 4 then ((Votes * 100) / ([total vote])) end) as LoserPercent,
    max(case when seqnum = 4 then party end) as LosingParty,
    max(case when seqnum = 4 then [first name]+ ' ' + [last name] end) as Loser,
    max(case when seqnum = 4 then CAST(winner AS tinyint) end) as LoserSelected




from 
(
select  
        r.title1,
        r.[precinct percent],
        r.[total vote],
        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]
FOR XML PATH ('ELECTION'), ROOT('root')

Is there a way I can have the select statement just "IGNORE" any fields that contain 0. Or choose to omit those that do contain 0 votes.

The total number of votes are being selected from the table "[RACE CANDIDATES]" if that helps. Hope it all makes sense.

Any input would be greatly appreciated.

Thanks All!

Adriaan Stander Adriaan Stander · Accepted Answer · 2014-03-13T04:15:07

You can either put in a where in the sub select

WHERE r.[total vote] != 0

or you could use a case statment

CASE WHEN r.[total vote] =  0 THEN 0 ELSE Votes / r.[total vote] END

XML won't output Divide by zero encountered in SQL Query

1 Answers