I was trying to get the nulls on doing a SUM() function on sql and I figured out that sql now consider nulls, so if a column has a different value and a null value the sum is good calculated. For example I have the column SUM_EXAMPLE with the different values: NULL – 3 – 4 – NULL – 6
So if I execute the next query: select sum(SUM_EXAMPLE) FROM TABLE T the result is 13 and not null.
And if I have another column SUM_COLUMNS only with NULL the result of select sum(SUM_COLUMNS) FROM TABLE T is NULL. It has been a change or a improvement of oracle so it does this function right without using functions like isNull or NVL?
Thank you
Advertisement
Answer
Aggregate functions (SUM, AVERAGE etc) generally ignore NULL values. Therefore if there is at least one non null value in the column, you will get a non null result.
If all values in a column being aggregated are null, there is pretty much no other option but to return null as the result.
Note that this is different to how scalar functions behave. For example: “Select x + y” will return null if either x, y or both are null. You will only get a non null result for this if both x and y have non-null values.