44

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?

Improve this question

3 Answers 3

Reset to default
77

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

39

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

Improve this answer

1 Comment

This solution suits better than the accepted answer when using CI active records. Thanks
-2

Try this one : SUM(IFNULL(rating, 0))

Improve this answer

1 Comment

This question has had well-rated answers for a very long time. Please edit and explain why this is better than existing ones.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.