I have three columns in postgresql

Here example from start_date No.1: 'Jan-01-2022' to 'August-03-2022': I will count only for August-2022 so the result for August-2022 is 5. No.6 the result Aug-2022 is 3. Result I wanna generate total_car_sales for whole table like this:

I have tried to use trunc_cate() but it is not works for it Any help for suggestion for me really appreciate it

Thank you

CodePudding user response：

Make a list of months (generate_series) and calculate total sales for each of them.

with the_table (no,total_car_sales,start_date,end_date) as 
(
 values
 (1,  5, 'Jan-01-2022'::date, 'Aug-03-2022'::date),
 (2,  1, 'April-01-2022', 'July-03-2022'),
 (3,  3, 'March-01-2022', 'May-03-2022'),
 (4,  7, 'Jan-01-2022',   'July-03-2022'),
 (5, 56, 'April-01-2022', 'April-25-2022'),
 (6,  3, 'April-01-2022', 'Aug-04-2022')
)
select 
  to_char(m, 'mon-yyyy') "month",
  coalesce
  (
   (select sum(total_car_sales) from the_table where m = date_trunc('month', end_date)), 
   0
  ) total_car_sales
from generate_series ('2022-01-01', '2022-08-01', interval '1 month') m;