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

User · Answer

You can also use COALESCE   expression       n     - returns first non-null like   SELECT COALESCE MAX X  0  AS MaxX FROM tbl WHERE XID   1

User · Answer

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

User · Answer

or   SELECT coalesce MAX X   0  AS MaxX FROM tbl WHERE XID   1

User · Answer

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

User · Answer

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

User · Answer

Oracle would be  SELECT NVL MAX X   0  AS MaxX FROM tbl WHERE XID   1

User · Answer

In SQL 2005   2008   SELECT ISNULL MAX X   0  AS MaxX FROM tbl WHERE XID   1

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

Examples related to sql

Examples related to sql-server