How to write sqlite select statement for NULL reco

Question

I have a column which contains null values in some of the rows.

I want to do sum of the column values by writing a select statement in sqlite.

How do I write the statement so that it treats null values as 0.

My current sqlite statement: select sum(amount) from table1 gives error as it returns null.

Please help.

Answer 1

You can use ifnull(x,y) or coalesce(x,y,z,...) function. Each of them return the first non-null value from the parameter list from left to right. ifnull has exactly two parameter whereas coalesce has at least two.

select sum(ifnull(amount, 0)) from table1

Answer 2

Accordgin to SQLite documentation:

The sum() and total() aggregate functions return sum of all non-NULL values in the group. If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the

So I guess you'd better use total() instead of sum() in case you expect that the result be 'non-null'.