Sum of column containing nan and inf as values in SQL

Question

I am using QuestDB and one column has values double e.g. 10.5, 5.2 etc, but few rows come like -nan, inf for not a number and infinity. What is the best way to sum this column. If I do normal sum it fails with casting problem due to nan and inf rows.

I can also use PostgreSQL if you think it can be solved in that.

Answer 1

You might want to try the KSUM function .

Alternatively, you could always use COALESCE to turn nulls into Zeroes.

Answer 2

Could you explain 'how normal sum fails with casting problem' ?

In QuestDB sum(double) skips null and NaN values but includes +/-Infinity.

Example (with psql because Web Console shows Infinities as null):

create table data ( d double);
insert into data values  (null), (NaN), (0);
select sum(d) from data;
 sum 
-----
 0.0    
(1 row)

Now, if we add Infinity:

insert into data values  ('Infinity');
select sum(d) from data;
   sum    
----------
 Infinity
(1 row)

What happens if we add -Infinity ?

insert into data values  ('-Infinity');
select sum(d) from data; sum 
-----
    
(1 row)

we get null, which is the same as NaN in QuestDB (see DATA TYPES), because we try to add -Infinity and +Infinity and that can't return a sensible result.

In addition to what Javier mentioned above, you also can skip Infinities with is not null:

select sum(d) from data where d is not null;
 sum 
-----
 0.0
(1 row)