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)

友情链接