# Tutorial :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)

### Solution:1

In SQL 2005 / 2008:

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

### Solution:2

or:

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

### Solution: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`

### Solution: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  ``

### Solution:5

Oracle would be

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

### Solution: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

### Solution: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  ``

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »