如果没有找到记录则返回零

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).

在此选择之后，我用另一个查询检索的整数减去res值并返回结果。如果验证WHERE子句，一切正常。但如果不是，我的所有函数返回都是一个空列（可能是因为我尝试减去一个空值的整数）。

How can I make my query return zero if the WHERE clause is not satisfied?

如果不满足WHERE子句，如何使我的查询返回零？

3 个解决方案

#1

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:

没有聚合的普通查询在这种情况下不会返回任何行。 COALESCE永远不会被召唤，也无法拯救你。在处理单个列时，我们可以包装整个查询：

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:

有关COALESCE（）的更多信息，请参见手册。有关手册中聚合函数的更多信息。在后面这篇文章中有更多选择：

How to return a value from a function if no value is found
如果没有找到值，如何从函数返回值

#2

I'm not familiar with postgresql, but in SQL Server or Oracle, using a subquery would work like below (in Oracle, the SELECT 0 would be SELECT 0 FROM DUAL)

我不熟悉postgresql，但是在SQL Server或Oracle中，使用子查询将如下所示（在Oracle中，SELECT 0将是SELECT 0 FROM DUAL）

SELECT SUM(sub.value)
FROM
( 
  SELECT SUM(columnA) as value FROM my_table
  WHERE columnB = 1
  UNION
  SELECT 0 as value
) sub

Maybe this would work for postgresql too?

也许这对postgresql也有效？

#3

You can also try: (I tried this and it worked for me)

你也可以尝试:(我试过这个，它对我有用）

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

#1