如果没有记录，如何使MySQL SUM查询返回零而不是null？

[英]How can I make a MySQL SUM query return zero instead of null if there are no records?

本文翻译自 Richard Knop 查看原文 2009/08/23 11412 mysql/ sql

Here is my select query:

这是我的选择查询：

SELECT SUM(rating) AS this_week 
FROM table_name 
WHERE UNIX_TIMESTAMP(created_at) >= UNIX_TIMESTAMP() - 604800)

Which basically counts the rating of an item for the last week (604800 is a number of seconds in 1 week).

这基本上计算了上周的项目评级（604800是1周内的秒数）。

The problem is that when there are no rows in the table, the this_week will be returned as NULL. I would like the query to return 0 in case there are no rows in the table. How to do it?

问题是当表中没有行时，this_week将返回NULL。我希望查询返回0，以防表中没有行。怎么做？

2 个解决方案

#1

This should do the trick:

这应该是诀窍：

SELECT COALESCE(SUM(rating),0) AS this_week FROM table_name 
  WHERE UNIX_TIMESTAMP(created_at) >= UNIX_TIMESTAMP() - 604800)

COALESCE is a function that will return the first non NULL value from the list.

COALESCE是一个函数，它将从列表中返回第一个非NULL值。

#2

Can't you use ISNULL(SUM(rating), 0)?

你不能使用ISNULL（SUM（评级），0）？

智能推荐

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2009/08/23/2dc023a92f11ab0f84adec244333eeb7.html。

猜您在找

如果Access 2013中没有匹配的记录，如何使查询返回0？ - How do I make query return 0 when no records match in Access 2013? 如何让此查询返回0而不是null？ - How can I get this query to return 0 instead of null? 如果查询在MySql中返回空值，如何设置0 ? - How can I set 0 if a query return a null value in MySql? 如何使用正则表达式返回带有MySQL查询的记录？ - How can I return records with a MySQL query using a regular expression? 如何更改此查询返回多个记录以返回包含原始记录串联的单个记录？ - How can I change this query returning multiples records to return a single record containing the concatenation of the original records?

赞助商链接