Android "null" from SUM

Question

I have a query that takes data from 2 tables. It works, but the two SUMs return NULL if there are no values. How can I get zero instead?

SELECT SUM(g.field1), 
       SUM(g.field2), 
       c._id, 
       c.field_1, 
       c.field_3 
  FROM Table1 g 
  JOIN Tabel2 c 
 WHERE ( c.field_1 = g.field5 )

Now if `SUM (g.field1), SUM (g.field2)` is 0 I get "null" instead I would get equally the result. I hope I explained — user3160725, Jan 09 '14 at 21:12
Duplicate of: http://stackoverflow.com/questions/799375/sqlite-equivalent-to-isnull-nvl-ifnull-or-coalesce — 323go, Jan 09 '14 at 21:12

score 13 · Accepted Answer · answered Jan 09 '14 at 21:54

13

The SUM functions returns NULL if there are no non-NULL values.

To get a return value of 0 even if there are no input values, just replace SUM with the TOTAL function:

SELECT TOTAL(g.field1), 
       TOTAL(g.field2), 
       ...

answered Jan 09 '14 at 21:54

CL.

173,858
17
217
259

score 0 · Answer 2 · edited May 23 '17 at 12:17

0

You can use IFNULL statement on your fields,

See this post

edited May 23 '17 at 12:17

Community

1
1

answered Jan 09 '14 at 21:11

Pierre

8,397
4
64
80

score 0 · Answer 3 · answered Jan 09 '14 at 21:21

0

What about SQlite coalesce() ?

The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must have at least 2 arguments.

In your case for every field you can use:

coalesce(c.field_X,0)

and the query will return 0 if c.field_X is NULL.

Please see http://www.sqlite.org/lang_corefunc.html

answered Jan 09 '14 at 21:21

elbuild

4,869
4
24
31

Ok I use `coalesce ()`, so if `(c.field_1 = g.field5)` is null, I would still run the query `SELECT c._id, c.field_1, c.field_3 FROM Tabel2 c` How can I? – user3160725 Jan 09 '14 at 21:41
I'm not following you, can you rephrase? How can (c.field_1 = g.field5) be null? It'd be either true or false, not NULL. If you want to check for equality including NULL check you can explode the IF condition to explicitely handle NULL vals. – elbuild Jan 09 '14 at 21:44
Si può essere perché nella clausola WHERE confronta `g.field5` con `c.field_1`, quindi se `c.field_1` è vuoto la query non dà risultati. A quel punto vorrei far eseguire solo `SELECT c._id, c.field_1, c.field_3 FROM Tabel2 c` – user3160725 Jan 09 '14 at 21:49
Let's keep with English for the others (I'm Italian as well) but The others are not :) The CONDITION can't be null (but the terms can as you explained). You basically want to ignore the condition if one of the value is null. You can modify the WHERE like this: WHERE (c.field_1 IS NULL OR g.field5 IS NULL OR c.field_1 = g.field5) – elbuild Jan 09 '14 at 22:00
Unfortunately, even so, if c.field_1, returns null. I could replace it with a subquery? but at that point as I run the cursor? – user3160725 Jan 09 '14 at 22:12

Android "null" from SUM

3 Answers3