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
45
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
9
Can't you use ISNULL(SUM(rating), 0)?
你不能使用ISNULL（SUM（评级），0）？
#1
45
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
9
Can't you use ISNULL(SUM(rating), 0)?
你不能使用ISNULL（SUM（评级），0）？