CODEWITHSUNDEEP
sql
HaloKu
HaloKu

Reputation: 435

Null in Aggregate Functions

https://modern-sql.com/concept/null#aggregates says:

Think About It How does the propagation of null through expressions and the removal of null values prior to aggregation affect the following expressions:

SUM(a+b)

SUM(a) + SUM(b)

I'm not really sure I understand what they mean and what is the difference.

I get that aggregation on NULL might give 0. But why there is a difference between these two statements?

Upvotes: 2

Views: 375

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Consider this data:

a     b
1     NULL
NULL  2
3     3

The expression sum(a + b) is going to return 6. Why? The subsums are:

1 + NULL  --> NULL
NULL + 2  --> NULL
3 + 3     --> 6

The sum() of these values is 6.

If you do the calculation sum(a) + sum(b), then the NULL values are combined in each column and you will get (1 + 3) + (2 + 3) --> 9.

Here is a db<>fiddle, illustrating this effect.

Upvotes: 2

Related Questions