Problem with gaps in data with generate_series

Question

I need a query that returns the cumulative sum of all paid bills per day in the current month.

I've tried a few codes, including this one:

SELECT DISTINCT 
         month.day,
         sum(bills.value) OVER (ORDER BY month.day)
FROM generate_series(1,31) month(day)
  LEFT JOIN bills ON date_part('day',bills.payment_date) = month.day
WHERE
(
(date_part('year',bills.payment_date)=date_part('year',CURRENT_DATE)) AND
(date_part('month',bills.payment_date)=date_part('month',CURRENT_DATE))
)
GROUP  BY month.day, bills.value, bills.payment_date
ORDER  BY month.day

I'm getting:

day  | value  
1    |  1000   
4    |  3000   
5    |  5000

The sum is correct, but I'm not getting all the 31 days from the generate_series function. Also, when I remove the DISTINCT command, the query just repeat the days, like:

day  | value  
1    |  1000   
4    |  3000   
4    |  3000  
4    |  3000  
4    |  3000  
5    |  5000   
5    |  5000

What I want is:

day  | value  
1    |  1000   
2    |  1000   
3    |  1000  
4    |  3000  
5    |  5000  
6    |  5000   
...  |  5000   
31   |  5000

Any ideas?

score 0 · Accepted Answer · 2019-11-06T15:06:20.493

Remove bills.value, bills.payment_date from the group by, then you also don't need the distinct any more. You can also simplify the WHERE clause. But you need to move that condition into the JOIN condition, otherwise it will turn the outer join back into an inner join.

SELECT month.day,
       sum(sum(bills.value)) over (order by month.day) as total_value
FROM generate_series(1,31) month(day)
  LEFT JOIN bills
        ON date_part('day',bills.payment_date) = month.day
       AND to_char(bills.payment_date, 'yyyymm') = to_char(current_date, 'yyyymm')
GROUP  BY month.day
ORDER  BY month.day

Problem with gaps in data with generate_series

1 Answers1