javascript - SQL Lite - SUM returns null instead of the 0 (zero) value - Stack Overflow

admin管理员组

文章数量:1023856

I have this SQL query:

var sqlQuery =
  "SELECT '"+dateRanges[ic].DATE_FROM+"' as DATE_FROM, "+
  " '"+dateRanges[ic].DATE_TO+"' as DATE_TO, "+
  " COUNT(*) AS DIALS_CNT, "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_APPT+"' THEN 1 ELSE 0 END) AS '"+APPT_CNT+"', "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_CONV_NO_APPT+"' THEN 1 ELSE 0 END) AS '"+CONVERS_CNT+"' , "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_CANNOT_REACH+"' THEN 1 ELSE 0 END) AS '"+CANNOT_REACH_CNT+"' "+
  " FROM "+DIALED_CALLS_TABLE+" dc "+
  " WHERE  dc.date BETWEEN '"+dateRanges[ic].DATE_FROM+"' AND '"+dateRanges[ic].DATE_TO+"';";

Problem is that if the sum of the values is zero result is containing value null. I thought that i can solve it using THEN 1 ELSE 0 END but it is not working.

How can i solve it please to get result with zeros instead of the null?

Thanks for any help.

I have this SQL query:

var sqlQuery =
  "SELECT '"+dateRanges[ic].DATE_FROM+"' as DATE_FROM, "+
  " '"+dateRanges[ic].DATE_TO+"' as DATE_TO, "+
  " COUNT(*) AS DIALS_CNT, "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_APPT+"' THEN 1 ELSE 0 END) AS '"+APPT_CNT+"', "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_CONV_NO_APPT+"' THEN 1 ELSE 0 END) AS '"+CONVERS_CNT+"' , "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_CANNOT_REACH+"' THEN 1 ELSE 0 END) AS '"+CANNOT_REACH_CNT+"' "+
  " FROM "+DIALED_CALLS_TABLE+" dc "+
  " WHERE  dc.date BETWEEN '"+dateRanges[ic].DATE_FROM+"' AND '"+dateRanges[ic].DATE_TO+"';";

Problem is that if the sum of the values is zero result is containing value null. I thought that i can solve it using THEN 1 ELSE 0 END but it is not working.

How can i solve it please to get result with zeros instead of the null?

Thanks for any help.

• javascript
• sqlite

Share Improve this question Follow edited May 19, 2020 at 20:38 apocalypse 5,8741010 gold badges5050 silver badges9797 bronze badges asked Dec 1, 2014 at 17:25 redromredrom 11.6k3434 gold badges166166 silver badges269269 bronze badges 3

• Just asking but.. How are you processing the query? Just wondering if it is safe enough to create a query in javascript that can easily be manipulated by the client resulting, therefore, in a possible easy-to-do injection. – briosheje Commented Dec 1, 2014 at 17:28
• Is it the Cordova mobile app. – redrom Commented Dec 1, 2014 at 17:28
• Oh, never heard about that. Anyway, maybe add that in the description, someone can surely help you with that knowing that you're using such a framework, it usually isn't safe to create sql queries through javascript itself. – briosheje Commented Dec 1, 2014 at 17:30

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 7

SUM returns NULL when it does not get any value to begin with, i.e., when your WHERE clause does not match any rows.

If you can live with the result being a floating-point number, replace SUM with TOTAL. Otherwise, you can put an IFNULL around the entire sum:

SELECT ... IFNULL(SUM(CASE ... END), 0) FROM ...

I think if some of your initiall values are null even the case/else won't work (null neither equals nor not-equals anything). Try putting some default, blank type value for nulls using IFNULL.

...
  " SUM(CASE WHEN IFNULL(dc.call_result,some_default_value) = '"+CALL_RESULT_STATE_APPT+"' THEN 1 ELSE 0 END) AS '"+APPT_CNT+"', "+
...

I have this SQL query:

var sqlQuery =
  "SELECT '"+dateRanges[ic].DATE_FROM+"' as DATE_FROM, "+
  " '"+dateRanges[ic].DATE_TO+"' as DATE_TO, "+
  " COUNT(*) AS DIALS_CNT, "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_APPT+"' THEN 1 ELSE 0 END) AS '"+APPT_CNT+"', "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_CONV_NO_APPT+"' THEN 1 ELSE 0 END) AS '"+CONVERS_CNT+"' , "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_CANNOT_REACH+"' THEN 1 ELSE 0 END) AS '"+CANNOT_REACH_CNT+"' "+
  " FROM "+DIALED_CALLS_TABLE+" dc "+
  " WHERE  dc.date BETWEEN '"+dateRanges[ic].DATE_FROM+"' AND '"+dateRanges[ic].DATE_TO+"';";

Problem is that if the sum of the values is zero result is containing value null. I thought that i can solve it using THEN 1 ELSE 0 END but it is not working.

How can i solve it please to get result with zeros instead of the null?

Thanks for any help.

I have this SQL query:

var sqlQuery =
  "SELECT '"+dateRanges[ic].DATE_FROM+"' as DATE_FROM, "+
  " '"+dateRanges[ic].DATE_TO+"' as DATE_TO, "+
  " COUNT(*) AS DIALS_CNT, "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_APPT+"' THEN 1 ELSE 0 END) AS '"+APPT_CNT+"', "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_CONV_NO_APPT+"' THEN 1 ELSE 0 END) AS '"+CONVERS_CNT+"' , "+
  " SUM(CASE WHEN dc.call_result = '"+CALL_RESULT_STATE_CANNOT_REACH+"' THEN 1 ELSE 0 END) AS '"+CANNOT_REACH_CNT+"' "+
  " FROM "+DIALED_CALLS_TABLE+" dc "+
  " WHERE  dc.date BETWEEN '"+dateRanges[ic].DATE_FROM+"' AND '"+dateRanges[ic].DATE_TO+"';";

Problem is that if the sum of the values is zero result is containing value null. I thought that i can solve it using THEN 1 ELSE 0 END but it is not working.

How can i solve it please to get result with zeros instead of the null?

Thanks for any help.

• javascript
• sqlite

Share Improve this question Follow edited May 19, 2020 at 20:38 apocalypse 5,8741010 gold badges5050 silver badges9797 bronze badges asked Dec 1, 2014 at 17:25 redromredrom 11.6k3434 gold badges166166 silver badges269269 bronze badges 3

• Just asking but.. How are you processing the query? Just wondering if it is safe enough to create a query in javascript that can easily be manipulated by the client resulting, therefore, in a possible easy-to-do injection. – briosheje Commented Dec 1, 2014 at 17:28
• Is it the Cordova mobile app. – redrom Commented Dec 1, 2014 at 17:28
• Oh, never heard about that. Anyway, maybe add that in the description, someone can surely help you with that knowing that you're using such a framework, it usually isn't safe to create sql queries through javascript itself. – briosheje Commented Dec 1, 2014 at 17:30

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 7

SUM returns NULL when it does not get any value to begin with, i.e., when your WHERE clause does not match any rows.

If you can live with the result being a floating-point number, replace SUM with TOTAL. Otherwise, you can put an IFNULL around the entire sum:

SELECT ... IFNULL(SUM(CASE ... END), 0) FROM ...

I think if some of your initiall values are null even the case/else won't work (null neither equals nor not-equals anything). Try putting some default, blank type value for nulls using IFNULL.

...
  " SUM(CASE WHEN IFNULL(dc.call_result,some_default_value) = '"+CALL_RESULT_STATE_APPT+"' THEN 1 ELSE 0 END) AS '"+APPT_CNT+"', "+
...

本文标签： javascriptSQL LiteSUM returns null instead of the 0 (zero) valueStack Overflow