SELECT max(x) is returning null; how can I make it return 0?

Question

How do you return 0 instead of null when running the following command:

SELECT MAX(X) AS MaxX
FROM tbl
WHERE XID = 1

(Assuming there is no row where XID=1)

Answer 1

or:

SELECT coalesce(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1

Answer 2

In SQL 2005 / 2008:

SELECT ISNULL(MAX(X), 0) AS MaxX
FROM tbl WHERE XID = 1

Answer 3

Like this (for MySQL):

SELECT IFNULL(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1

For MSSQL replace IFNULL with ISNULL or for Oracle use NVL

Answer 4

You can also use COALESCE ( expression [ ,...n ] ) - returns first non-null like:

SELECT COALESCE(MAX(X),0) AS MaxX
FROM tbl
WHERE XID = 1

Answer 5

Oracle would be

SELECT NVL(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1;

Answer 6

For OLEDB you can use this query:

select IIF(MAX(faculty_id) IS NULL,0,MAX(faculty_id)) AS max_faculty_id from faculties;

As IFNULL is not working there

Answer 7

Depends on what product you're using, but most support something like

SELECT IFNULL(MAX(X), 0, MAX(X)) AS MaxX FROM tbl WHERE XID = 1

or

SELECT CASE MAX(X) WHEN NULL THEN 0 ELSE MAX(X) FROM tbl WHERE XID = 1

Answer 8

For my case using max() was creating problem with group by even on outer SELECT statement.

So only thing that saved my day was following by avoiding adding other columns in group by clause or using aggregate on other columns. So I wrote on outer SELECT statement as following:

SELECT username, case when total_post_comments is null then 0 else total_post_comments end total_post_comment FROM (subquery)

Even not directly related to this I hope it will help other people.