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