oracle SELECT SUM 在没有记录时返回一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2436992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT SUM returns a row when there are no records
提问by Jose L Martinez-Avial
I'm finding some problems with a query that returns the sum of a field from a table for all the records that meet certain conditions. I expected to receive a "No records found' when there were no records, but instead I'm receiving a null result.
我发现一个查询存在一些问题，该查询返回满足特定条件的所有记录的表中字段的总和。我希望在没有记录时收到“未找到记录”，但我收到的是空结果。
SQL> SELECT * FROM DUAL WHERE 1=2;
no rows selected
SQL> SELECT SUM(dummy) FROM DUAL WHERE 1=2;
SUM(DUMMY)
----------
SQL>
Is there any way to not receive any record in that case?
在这种情况下，有没有办法不接收任何记录？
采纳答案by Jeffrey L Whitledge
How about this:
这个怎么样：
select my_sum
from
(SELECT SUM(dummy) as my_sum FROM DUAL WHERE 1=2)
where
my_sum is not null
回答by Gary Myers
"I expected to receive a "No records found' when there were no records, but instead I'm receiving a null result."
“当没有记录时，我希望收到“未找到记录”，但我收到的是空结果。”
Then do
然后做
SELECT SUM(dummy) FROM DUAL WHERE 1=2 HAVING COUNT(*) > 0
That is, specify that you only want to return a summary where there were rows that were considered.
也就是说，指定您只想返回包含已考虑行的摘要。
SELECT SUM(dummy) FROM DUAL WHERE 1=2 HAVING SUM(dummy) IS NOT NULL
is similar, but the COUNT(*) would return a summary row if there were only rows for which dummy was null, while the latter would not.
类似，但如果只有 dummy 为空的行，则 COUNT(*) 将返回汇总行，而后者不会。
回答by Wolph
You can filter out the nullresults with having
您可以使用以下方法过滤null结果having
SELECT SUM(dummy) FROM DUAL WHERE 1=2 HAVING SUM(dummy) IS NOT NULL
回答by Itay Moav -Malimovka
No - this is the behavior by design of the RDBMS in use here, Which, to me atleast, makes sense, as you are looking for a sum and not raw data
不 - 这是此处使用的 RDBMS 的设计行为，至少对我来说，这是有道理的，因为您正在寻找总和而不是原始数据
回答by Vlad Patryshev
The sum of zero numbers is equal to 0 (that's math). So it would be only natural for select sum(something) to return 0 if there are no records to sum, similarly to select count(*) that shouldreturn 0 if the count is 0 (no records satisfying the predicate). That's in an ideal world of course.
零数字的总和等于 0（这是数学）。因此，如果没有要求和的记录，则 select sum(something) 返回 0 是很自然的，类似于 select count(*)如果计数为 0（没有满足谓词的记录）应返回 0。那当然是在一个理想的世界里。
回答by Peter
use the coalesce function of mysql and do:
使用mysql的coalesce功能并执行：
SELECT COALESCE(SUM(where),0) FROM table WHERE condition = variable
回答by Hong Van Vit
SELECT SUM(dummy) FROM DUAL GROUP BY 1
回答by velop
You can group by the another metric. For example month and then sql also returns 0 rows:
您可以按另一个指标进行分组。例如月份然后 sql 也返回 0 行：
SELECT SUM(dummy), DATE_FORMAT(day, '%Y-%m') as month
FROM DUAL WHERE 1=2
GROUP BY month
