Sum monthly amount and output for last day of month

Question

I have table like example 1

here is summed amount by day. i need sum amount by month and join that value for last day of month, pls see image example 2

Thanks

Answer 1

Try this:

SELECT t.amount, t.dt, CASE WHEN month_cnt = rn THEN s ELSE NULL END AS month_s FROM (
    select your_table.*, 
    sum(amount) over(partition by year(dt), month(dt) ) s,
    count(*) over(partition by year(dt), month(dt)) month_cnt,
    ROW_NUMBER() over(partition by year(dt), month(dt)  order by dt) rn

    from your_table 
)t 
order by dt