Wednesday, December 29, 2010

SUM with NULL Values in Teradata

In Teradata, when you SUM a value that is nullable, does it work correctly if there are null values? Here's the test and the result. The answer is yes, it works fine.

CREATE VOLATILE TABLE rgWork1 (
    
client      INT         NOT NULL,
    
flag        byteint 

) UNIQUE PRIMARY INDEX (client) 
ON COMMIT PRESERVE ROWS;

INSERT INTO rgWork1 (client, flag) VALUES (1, 1); 

INSERT INTO rgWork1 (client, flag) VALUES (2, 0);
INSERT INTO rgWork1 (client, flag) VALUES (3, NULL);

SELECT SUM(flag)

FROM rgWork1;
 

SELECT SUM(flag)
FROM rgWork1 

WHERE flag = 1;
 

SELECT SUM(flag)
FROM rgWork1

WHERE flag IS NOT NULL;
 

SELECT SUM(flag)
FROM rgWork1

WHERE (flag = 1 OR flag IS NOT NULL);

All of these queries return the correct answer of 1.

No comments:

Post a Comment