I've been using SQL in various forms for 20 years a very long time but I still don't consider myself to be a particular export. I've found out the hard way some pretty handy performance guidelines and helped folks over little hurdles but I love it when I'm trying to sort out a problem and discover something I didn't know (but it's probably really obvious if only I ever read a manual!)

Todays little challenge was fun. I wanted to display all records in a table where we recorded a total vote and the number of votes in descending order of average vote.

The problem was I needed to calculate that average (total vote divided by number of votes) but some of the rows had not received any votes so the result would be a divide by zero error.

The solution turned out to be a CASE statement inside the SELECT. I've used COALESCE before to zero out a null column in a query but this is so handy I wish I'd known about it before!

So, an example of using the case statement in a query to solve my problem. The rate_total and rate_vote_count columns are both integer in the database so I needed to cast them to decimal firstly so the math would return a decimal and then truncate as I only cared about one decimal place.

My query ended up something like this: 

SELECT     rate_id, CAST(CAST(rate_total AS decimal(8, 1)) / 
        CASE WHEN rate_vote_count = 0 THEN 1 ELSE rate_vote_count END
        AS decimal(2, 1))  AS rate_avg
FROM         upload_rate WITH (nolock)
ORDER BY rate_avg DESC

It's not the pretties code by any stretch of the imagination but the magic is on the second line....

if the rate_vote_count is zero then the CASE returns 1 (to avoid a divide by zero error, but 0/1 is the same as 0 so the query works as wanted) and if it's 1 or greater than it returns the value in the column enabling the maths to be performed correctly.

Another handy thing to file away