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)

Vo

Answer 1

In SQL 2005 / 2008:

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

Entrain

Answer 2

or:

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

Octagon

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

Hefty

In SQL Server it would be ISNULL. I don't know if it is a typo or a valid command in other SQL dialects. – Sternick

The OP doesn't specify: MySQL uses IFNULL – Hefty

I was, possibly incorrectly, assuming SQL Server! So of course, you may NOT have meant ISNULL!! – Victorie

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

Straightway

Answer 5

Oracle would be

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

Rudbeckia

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

Tonguelash

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

Leban

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.

Knopp