If you want to add (SUM) a set of rows and your result does not shows anything (or shows a NULL value), then there might be a NULL value present somewhere in between. As you know the reference manual states that any number plus a NULL would always result in a NULL, like 10 + NULL = NULL. Also aggregate functions such as SUM, COUNT ignore NULLs. In this case how would you SUM a column with NULL value present? There are various options, one way is to disallow NULLs in the table definition and the other way is to use the ZEROIFNULL function. Let’s work out an example.

1. First Create Volatile Table, note that Col2 is made NOT NULL to avoid NULL Values, which is one of our solutions to add a NULL to a non NULL.


CREATE SET VOLATILE TABLE A1 , NO FALLBACK ,
CHECKSUM = DEFAULT,
LOG
(
RecNum INTEGER,
Col1 INTEGER,
Col2 INTEGER NOT NULL
)
PRIMARY INDEX (RecNum)
ON COMMIT PRESERVE ROWS;

2. Insert a few rows as follows, Note in the 4th statement Col1 is blank.

INSERT INTO A1 values (1,2,3);
INSERT INTO A1 values (2,4,5);
INSERT INTO A1 values (3,6,6);
INSERT INTO A1 values (4, ,4);
INSERT INTO A1 values (5,5,6);

3. Run the following query:

SELECT RecNum, SUM(Col1) ,SUM(Col2) ,SUM(Col1+Col2)
FROM A1
GROUP BY RecNum
ORDER BY RecNum;

4. The above query would give following results. Note the NULL values

RecNum Sum(Col1) Sum(Col2) Sum((Col1+Col2))

1                  2                  3        5
2                  4                  5        9
3                  6                  6        12
4                  NULL             4        NULL
5                  5                  6        11

5. Run the same query with ZEROIFNULL functions.


SELECT RecNum, SUM(ZEROIFNULL(Col1)) ,SUM(Col2) ,SUM(ZEROIFNULL(Col1)+Col2)
FROM A1
GROUP BY RecNum
ORDER BY RecNum;

6. The above query would give following results. Note the NULL values disappeared.

RecNum Sum(ZeroIfNull(Col1)) Sum(Col2) Sum((ZeroIfNull(Col1)+Col2))

1                  2                           3                  5
2                  4                           5                  9
3                  6                           6                  12
4                  0                           4                  4
5                  5                           6                  11

If you want to change the NULL values to something other than NULL, here are the commands:

1. To replace with your favorite string, you can use:

SELECT COALESCE(Col1,’JustForFun’) Replaced_Null
FROM A1

Replaced_Null
JustForFun
JustForFun
    2
    6
    5

2. Or to replace the NULL with a numeric value while adding,

SELECT RecNum Records,
           SUM(COALESCE(Col1,50)) Col1,
           SUM(Col2) Col2,
           SUM(COALESCE(Col1,50)+Col2) Results
FROM A1
GROUP BY RecNum
ORDER BY RecNum;

Records Col1    Col2    Results

1          2        3        5

2          50      5        55

3          6        6        12

4          50      4        54

5          5        6        11

If you did not find what you are looking for, would you like to leave a comment with your question? We will try our best to address it soon. Do not forget to mention your DB environment.

Our Random Articles

More Links