MySQL View: How do I set default value of a calculated field to 0?

Question

I use the following statement to create a percentage calculation in a view:

round(((Surveys.RejectedAsAdvertising / Surveys.Responses) * 100),0) AS PercentageAdvertising

Basically, I divide the number of responses to a certain question (in this case, is something considered to be advertising) by the total number of responses.

In the end, I have columns that show percentages for each of five possible survey responses ("Is it advertising?", "Is it boring?", etc.).

This works perfectly but there's one thing I'd like to tweak. When a response type (e.g., advertising) does not have any votes, my calculated field receives a NULL value.

When defining my view, how can I specify a default value of 0?

Thanks.

Answer 1

You need a coalesce in there. It selects the first non-NULL argument, so you need something like:

coalesce(
    round(
        ((Surveys.RejectedAsAdvertising / Surveys.Responses) * 100)
        ,0)
    ,0)
    AS PercentageAdvertising

Then, when the result of the round is NULL, it will give you zero instead.

Answer 2

COALESCE is the preferred approach, because it is an ANSI standard way of handling evaluation when a parameter is null. Being ANSI, the same syntax is supported on Oracle, SQL Server, Postgres, etc., making the query easier to port between databases if needed.

COALESCE(round(((Surveys.RejectedAsAdvertising / Surveys.Responses) * 100),0) AS PercentageAdvertising, 0)

IFNULL is the MySQL specific syntax:

IFNULL(round(((Surveys.RejectedAsAdvertising / Surveys.Responses) * 100),0) AS PercentageAdvertising, 0)