Question 272890 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Sum of column containing nan and inf as values in SQL

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.

Tags:

2

Answers


  1. 0

    You might want to try the KSUM function .

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

    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search