SELECT max(x) is returning null; how can I make it return 0?
SqlSql ServerSql Problem Overview
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)
Sql Solutions
Solution 1 - Sql
or:
SELECT coalesce(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1
Solution 2 - Sql
In SQL 2005 / 2008:
SELECT ISNULL(MAX(X), 0) AS MaxX
FROM tbl WHERE XID = 1
Solution 3 - Sql
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
Solution 4 - Sql
You can also use COALESCE ( expression [ ,...n ] ) - returns first non-null like:
SELECT COALESCE(MAX(X),0) AS MaxX
FROM tbl
WHERE XID = 1
Solution 5 - Sql
Oracle would be
SELECT NVL(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1;
Solution 6 - Sql
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
Solution 7 - Sql
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