Count orders 12 months back from a certain month group by that month I want to create a table with months counting orders from current month to 12 months back. I have data with a create_date and an order_id. So I need to convert the date to a format of yyyymm and count all order from selected month and sum up all orders that where made until 12 months back.

The outcome should look like:



I started with this query but it only counts orders for the selected month and not includes orders for former months.

SELECT TO_CHAR(create_date,'yyyymm') AS yyyymm,
       COUNT(order_id ) AS Number_of_orders_12_month_period
FROM Orders 
WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create_date) AND TO_CHAR(create_date,'yyyymm')  
group by 1
I don't have experience with Redshift but

WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create__date) AND TO_CHAR(create_date,'yyyymm')  


looks wrong to me. You don't want to compare create_date with itself, you want to compare it with current_date, and you want to compare after each date is converted to a month. For instance:

WHERE TO_CHAR(create_date,'yyyymm') BETWEEN TO_CHAR(DATEADD(MONTH,-12,current_date),'yyyymm') AND TO_CHAR(current_date,'yyyymm')