How to detect NULL rows in PostgreSQL sum()

Question

I want to aggregate the sum of a column, while tracking the presence of NULL values which signal an error condition. E.g., take the table numbers:

# select * from numbers;
  n   |  l  
------+-----
    1 | foo
    2 | foo
 NULL | bar
    4 | bar

Given a label l, I want to compute the sum of numbers n with that label, provided there are no NULL values. Ideally, for a label without any rows, the sum would be 0. So I'm looking for some query q such that q('foo') = 3, q('baz') = 0 and q('bar') somehow signals an error, e.g. by returning NULL.

I started with the sum() aggregate function, but that converts NULL rows to 0. One solution would be a variant that returns NULL provided there are any NULL values.

sum() gives

# select sum(n) from numbers where l = 'bar';
 sum 
-----
   4

but I'd rather have sumnull() with

# select sumnull(n) from numbers where l = 'bar';
 sumnull 
---------
  NULL

The best solution I've found so far is to also count non-NULL rows and compare to the total count:

# select sum(n), count(*), count(n) as notnull from numbers;
 sum | count | notnull 
-----+-------+---------
   7 |     4 |       3

Then if count is unequal to notnull, I know the result is not valid.

I am not aware of any standard SQL function to do that. But you can easily create such an aggregate function. — user330315
– user330315, Commented Sep 26, 2016 at 13:03
Additionally to the answers below. Sometimes it could be more efficiency to split the task into the two steps: 1) check is the null value exists (using an index on the column) and if not 2) calculate the sum. — Abelisto
– Abelisto, Commented Sep 26, 2016 at 13:48

Clodoaldo Neto · Accepted Answer · 2016-09-26 13:59:51Z

14

Is an empty set good enough?

create table numbers (n int);
insert into numbers values (1),(2),(null),(4);

select sum(n)
from numbers
having bool_and(n is not null);
 sum 
-----
(0 rows)

If you really need a null value it is a bit more complex:

with sum_null as (
    select sum(n) as sum_n
    from numbers
    having bool_and(n is not null)
)
select case
    when not exists (select 1 from sum_null) then null
    else (select sum_n from sum_null) end
;
 sum_n 
-------

(1 row)

Replacing the having line for:

having not bool_or(n is null)

is less readable but potentially much faster since it can stop searching at the first null found.

https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE

edited Sep 26, 2016 at 13:59

answered Sep 26, 2016 at 13:31

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

robx Over a year ago

You're right, an empty result set is actually a more useful result for signalling an error condition. One nitpick is that I would regard an empty table as fine and expect the sum 0, though clearly I didn't put that into the question. This can be solved by changing to having coalesce(bool_and(n is not null), true).

Community · Accepted Answer · 2017-05-23 12:00:32Z

8

You can create a custom aggregate, e.g.:

create or replace function int_sum_null(int, int)
returns int language sql as $$
    select $1 + $2
$$;

create aggregate sumnull(integer) (
    sfunc = int_sum_null,
    stype = int
);

select sum(n), sumnull(n)
from numbers;

 sum | sumnull 
-----+---------
   7 | <null>      
(1 row)

Update #1

Solutions without a custom aggregate:

select case 
    when bool_or(n is null) then null 
    else sum(n) end
from numbers;

select coalesce((
    select sum(n)
    from numbers
    having not bool_or(n is null)), null);

These variants are based on the Clodoaldo Neto idea. If you like them please upvote his answer too.

Update #2

Modify the custom aggregate sumnull and add the initial condition:

drop aggregate sumnull(integer);
create aggregate sumnull(integer) (
    sfunc = int_sum_null,
    stype = int,
    initcond = 0
);

to get the results you have described in the updated question:

create table numbers (n int, l text);
insert into numbers values 
(1, 'foo'), (2, 'foo'), (null, 'bar'), (4, 'bar');

select 
    sumnull(n) filter (where l = 'foo') foo, 
    sumnull(n) filter (where l = 'bar') bar, 
    sumnull(n) filter (where l = 'baz') baz
from numbers;

 foo | bar | baz 
-----+-----+-----
   3 |     |   0
(1 row)

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Sep 26, 2016 at 13:11

klin

123k15 gold badges241 silver badges263 bronze badges

4 Comments

Laurenz Albe Over a year ago

You could replace int with anyelement.

Abelisto Over a year ago

@LaurenzAlbe IMO not anyelement but numeric

klin Over a year ago

@LaurenzAlbe - yes, this is possible, however system aggregates are defined for individual types. There is a difference in error reporting when an aggregate is used improperly.

fgblomqvist Over a year ago

Worth noting is that the custom aggregate (sumnull) is incredibly slower than the built-in sum (in my case, about 10x). In case you're looking for performance, the case statement is a much better solution.

Maxim · Accepted Answer · 2018-12-21 09:28:29Z

3

1) Use COALESCE

SELECT SUM(COALESCE(n,'NaN'::numeric)) FROM numbers;

If any row is NULL then result will be as 'NaN'

2) You will get NULL as result if any row has NULL value else result as number

SELECT 
    CASE WHEN (SELECT COUNT(*) FROM numbers WHERE n IS NULL) > 0 THEN NULL
        ELSE (SELECT SUM(COALESCE(n, 0)) FROM numbers)
END

edited Dec 21, 2018 at 9:28

answered Dec 5, 2018 at 10:06

Maxim

2,40110 silver badges17 bronze badges

Comments

Felk · Accepted Answer · 2018-10-11 16:26:54Z

2

You can have it return NaN for those cases by using coalesce to replace NULL with NaN;

create table t(
  val int
);

insert into t (val) values (1), (2), (NULL);
select sum(coalesce(val, double precision 'NaN')) from t;

Result: NaN.

insert into t (val) values (1), (2), (3);
select sum(coalesce(val, double precision 'NaN')) from t;

Result: 6.

See this SQLFiddle

answered Oct 11, 2018 at 16:26

Felk

8,3032 gold badges41 silver badges79 bronze badges

Comments

Stephen Chung · Accepted Answer · 2024-04-15 13:48:53Z

0

I have the following... just in case it is useful to somebody:

SELECT SUM(n) * CASE WHEN count(n) = count(*) THEN 1 ELSE NULL END FROM numbers

The trick is to detect whether there are NULL rows by comparing counts, then forcing a math operation with either 1 or NULL which yields a NULL when there are NULL rows.

answered Apr 15, 2024 at 13:48

Stephen Chung

14.6k1 gold badge37 silver badges48 bronze badges

Collectives™ on Stack Overflow

How to detect NULL rows in PostgreSQL sum()

5 Answers 5

1 Comment

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related