When summarizing multiple columns, NULL appears in the SQL query. Prompt, pliz how to fix it. ISNULL is not helping.

Closed due to the fact that the essence of the question is incomprehensible to the participants tutankhamun , pavel , aleksandr barakin , Firepro , Mirdin Sep 11 '16 at 18:00 .

Try to write more detailed questions. To get an answer, explain what exactly you see the problem, how to reproduce it, what you want to get as a result, etc. Give an example that clearly demonstrates the problem. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • Decide on the labels, is it postgresql or mysql after all? - VenZell
  • 3
    Something like a more detailed description of the problem, extrasensory abilities today are not very good, and without them it is impossible to understand what you are summing up with and what’s more then isnull - Mike
  • Thank! Something does not work anyway (In general, I have data on user costs, where the users are in rows, and in the columns of the month. I need to add up both in rows and columns to get the total amount. I use this query: ( SUM (CAST (Replace ([Column 1], '$', '') as FLOAT)) + SUM (CAST (Replace ([Column 2], '$', '')), but with the addition of the third column, NULL is issued. The data is in text form ($ 100.00), so I replace it with a numeric one. What could be the problem when calculating the entire amount? Prompt, correct query - - Carol

2 answers 2

I think you need IFNULL :

MySQL Table:

 +---+------+---+ | a | b | c | +---+------+---+ | 1 | NULL | 2 | +---+------+---+ | 2 | 10 | 5 | +---+------+---+ | 3 | NULL | 4 | +---+------+---+

Queries:

 SELECT a + IFNULL(b,0) + c AS result FROM table; SELECT SUM(a + IFNULL(b,0) + c) AS result FROM table;
  • Thank! Something does not work anyway (In general, I have data on user costs, where the users are in rows, and in the columns of the month. I need to add up both in rows and columns to get the total amount. I use this query: ( SUM (CAST (Replace ([Column 1], '$', '') as FLOAT)) + SUM (CAST (Replace ([Column 2], '$', '')), but with the addition of the third column, NULL is issued. The data is in text form ($ 100.00), so I replace it with a numeric one. What could be the problem when calculating the entire amount? Prompt, correct query - Carol

SUM should ignore NULL values ​​when summing.

For example, if you set SUM on a column where the values ​​are 1, NULL, 2, then the result should be 3.

Another thing, if you use a condition that returns nothing, then only in this case it can be NULL.

  • we first check for NULL, and then the remaining actions: select sum (cast (replace (IFNULL ([Column 1], '0'), '$', '') as FLOAT) + sum (cast (replace (IFNULL ([Column 2], '0'), '$', '') as FLOAT) + sum (cast (replace (IFNULL ([Column 3], '0'), '$', '') as FLOAT) from .. - DSKalugin