CODEWITHSUNDEEP
sqlmysql
Richard Knop
Richard Knop

Reputation: 83677

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

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?

Upvotes: 43

Views: 28443

Answers (3)

Leon Tang
Leon Tang

Reputation: 1

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

Upvotes: -2

Christian Merat
Christian Merat

Reputation: 4304

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

Upvotes: 38

Jimmy Stenke
Jimmy Stenke

Reputation: 11220

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.

Upvotes: 76

Related Questions