Return zero if no record is found

Question

I have a query inside a stored procedure that sums some values inside a table: ``` SELECT SUM(columnA) FROM my_table WHERE columnB = 1 INTO res; ``` After this select I subtract `res` value with an integer retrieved by another query and return the result. If `WHERE` clause is verified, all works fine. But if it's not, all my function returns is an empty column (maybe because I try to subtract a integer with an empty value). How can I make my query return zero if the `WHERE` clause is not satisfied?

Accepted Answer

You could:

```
SELECT COALESCE(SUM(columnA), 0) FROM my_table WHERE columnB = 1
INTO res;
```

This happens to work, because your query has an aggregate function and consequently always returns a row, even if nothing is found in the underlying table.

Plain queries without aggregate would return no row in such a case. `COALESCE` would never be called and couldn't save you. While dealing with a single column we can wrap the whole query instead:

```
SELECT COALESCE( (SELECT columnA FROM my_table WHERE ID = 1), 0)
INTO res;
```

Works for your original query as well:

```
SELECT COALESCE( (SELECT SUM(columnA) FROM my_table WHERE columnB = 1), 0)
INTO res;
```

More about `COALESCE()` in the manual. More about aggregate functions in the manual. More alternatives in this later post:

- How to return a value from a function if no value is found

Return zero if no record is found

Solution

Problem