CODEWITHSUNDEEP
sqlpostgresql
Angga Prasetiyo
Angga Prasetiyo

Reputation: 11

How to increment value when one of row have NULL value in SQL

I have a table "Total Product" that one of some rows has NULL values for the total product. I'd like to create a query that can increment all value even it is NULL. This is what table 

| Date      | Total Product Added | 
|---------- |-------------------- | 
| 18/07/01  |         100         |
| 18/05/01  |         300         |
| 18/01/01  |        1000         |

this what table that i was expected

| Date      | Total Product Added |
|---------- |-------------------- |
| 18/07/01  |        1400         |
| 18/06/01  |        1300         |
| 18/05/01  |        1300         |
| 18/04/01  |        1000         |
| 18/03/01  |        1000         |
| 18/01/01  |        1000         |

i already used this query but the result not like i want:

select
    date,
    sum(total_product_added) over (order by date asc) as total_product,

from
    sold_product

group by
    date,
    total_product
order by
    date_created desc

when i have used that query, this is the result:

| Date      | Total Product Added |
|---------- |-------------------- |
| 18/07/01  |        1400         |
| 18/05/01  |        1300         |
| 18/01/01  |        1000         |

Upvotes: 0

Views: 246

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Use generate_series():

select gs.dte,
       sum(total_product_added) over (order by gs.dte asc) as total_product
from (select generate_series(min(sp.date), max(sp.date), interval '1 day') as dte
      from sold_product sp
     ) gs left join
     sold_product sp
     on sp.date = gs.dte
order by gs.dte;

Here is a db<>fiddle.

Upvotes: 1

ecp
ecp

Reputation: 327

Use COALESCE:

select
    date,
    sum(COALESCE(total_product_added, 0)) over (order by date asc) as total_product,    
from
    sold_product    
group by
    date,
    total_product
order by
    date_created desc

Upvotes: 0

Related Questions