Select Max(X) Is Returning Null; How to Make It Return 0

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

In SQL 2005 / 2008:

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

SELECT MAX(...) incorrectly returns NULL in stored procedure

That's probably because of this line:

  declare created_timestamp timestamp;

Checking SQL MAX() Function returning null

An aggregate function like MAX() will always return one row per group. In your case, your group is the whole table. Therefore you get a one row resultset with the MAX value. Since you don't have data in your table, the MAX value is undefined, therefore NULL.

For better understanding try to replace MAX with COUNT. This will also return one row but with value 0. I think this is more intuitive and will help you better to understand what is happening.

How set 0 with MAX function when it is NULL?

Well, as there is no date like 2014, you would expect null, because the maximum of nothing is actually not anyting.

But do this:

COALESCE(MAX(number),0)

Which means: get the first non-null thing from the next list, so if your max is null, it'll give you 0

SELECT MAX(id) in an empty table returns NULL instead of 0

Use COALESCE to replace NULL expression with something else:

SELECT COALESCE(MAX(id), 0) + 1 FROM table

I would suggest using an AUTO_NUMBER field instead of generating the id.

SQL max function returning NULL value

Your query code is basically fine, but I would write it as:

SELECT
    Statecode,
    MIN(Median_household_income) as Minimumincome,
    MAX(Median_household_income) as MaximumIncome,
    AVG(Median_household_income) as AverageIncome
FROM census
GROUP BY Statecode;

Note: No single quotes around the column names. Only use single quotes for STRING and DATE/TIME constants.

The issue is your table creation. SQLite is already confused enough about the types of columns (it basically ignores the declarations and stores whatever you put there). Your code makes it worse, because you are confusing numbers and strings. Here is an example:

INSERT INTO "main"."census" ("Statecode", "Population", "Median_household_income")
    VALUES ('PR', '17800', '11507');

(I would also advise you to drop the double quotes. They just make queries harder to write and to read.)

You have defined Population and Median_household_income as integers. You should insert them as integers, not strings:

INSERT INTO "main"."census" ("Statecode", "Population", "Median_household_income")
    VALUES ('PR', 17800, 11507);

Later on, you have examples like this:

INSERT INTO "main"."census" ("Statecode", "Population", "Median_household_income")
VALUES ('MA', '237', 'NULL');

This confusion is even worse with NULL. NULL and 'NULL' are two very different things. The first is a SQL constant that represents an unknown value (often used for missing values). The second is a string with four characters, such as 'Ohio' or 'love'. You intend NULL.

And, if you use that, the code will work. I am a bit confused as to why this returns a value for MIN(), but not for MAX() -- as I say, SQLite has quite confusing (to me) typing rules, so there is probably some reason. However, if you stick with the correct types then you won't have this problem.

EDIT:

There seems to be a bug with SQLite and typing. This expression -- shockingly -- returns 'text' for max() and 'integer' for min():

select typeof(max("Median_household_income")), 
       typeof(min("Median_household_income"))
from census

SQLite has such arcane typing rules that this can happen. The MIN() and MAX() are based on the ordering of the column. The type appears to follow whatever the first or last type is based on the column ordering.

How to treat MAX() of an empty table as 0 instead of NULL

Just use Coalesce or NVL to handle NULLs.

The following code will return 0 if MAX(cid) is NULL

SELECT COALESCE(MAX(cid), 0)
FROM   itemconfiguration

---

Related Topics