René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
sum aggregate function [Oracle SQL] | ||
sum is an aggregate function. It returns the sum of its numeric-expressions for each group indicated in the
group by clause.
sum() containing nulls
When null is used in a mathematical expression in SQL, the result is null, as for example in:
5+7+NULL+9 ----------
However, if a sum() is created over some numbers, nulls are disregarded as the following example shows:
create table group_by_sum ( a number, b number ); insert into group_by_sum values ( 1, 1); insert into group_by_sum values ( 10, 1); insert into group_by_sum values ( 100, 1); insert into group_by_sum values ( 1000, 1); insert into group_by_sum values ( 2, 2); insert into group_by_sum values ( 20, 2); insert into group_by_sum values ( 200, 2); insert into group_by_sum values ( 2000, 2); insert into group_by_sum values ( null 2); select sum(a), b from group_by_sum group by b; SUM(A) B ---------- ---------- 1111 1 2222 2 |