How to query increased sales every day a month in SQL Redshift?

Question

Can some 1 help me with SQL redshift query to get the result the way mentioned below

My table:

SalesDate  | Amount($)

2022-03-01 | 4     
2022-03-01 | 5     
2022-03-02 | 3     
2022-03-02 | 10     
2022-03-02 | 12      
2022-03-03 | 1    
 etc..

I want to have an increased sales table group by SalesDate :

SalesDate  | Amount($)

2022-03-01 | 9    
2022-03-02 | 34   
 etc...

Currently, I tried to use this query but doesn't work:

select distinct salesdate::date as date_number
, sum(*) over (order by salesdate::date) asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as amount
from mytable
where salesdate >= '2022-03-01'

So I received the result not as my wanted. It increase but not as my wanted:

SalesDate  | Amount($)

2022-03-01 | 4

2022-03-01 | 9  

2022-03-02 | 12

2022-03-02 | 22

2022-03-02 | 34

Answer 1

You can try to use group by with aggregate function in a subquery, before use window function.

SELECT date_number,
       sum(amount) over (order by date_number) totalAmount
FROM (
    select salesdate::date as date_number, 
           sum(Amount) as amount
    from mytable
    where salesdate >= '2022-03-01'
    GROUP BY salesdate::date
) t1