Help with MySQL SUM()

Question

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

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?

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

Answer 2

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

Answer 3

You could select COUNT() as well, and check that value first before checking your ratings.

print ($count == 0) ? "0" : $ratings ;