How to create SQL based on complex rule?

Question

I have 3 columns (id, date, amount) and trying to calculate 4th column (calculated_column).

How to create SQL query to do following:

The way that needs to be calculated is to look at ID (e.g. 1) and see all same IDs for that month (e.g. for first occurrence - 1-Sep it should be calculated as 5 and for second occurrence - it would be 5+6=11 -> all amounts from beginning of that month including that amount).

Then for the next month (Oct) - it will find first occurrence of id=1 and store 3 in calculated_column and for the second occurrence of id=1 in Oct it will do sum from beginning of that month for the same id (3+2=5)

Answer 1

Assuming I've understood correctly, I would suggest a correlated subquery such as:

select t.*, 
(
    select sum(u.amount) from table1 u  
    where 
        u.id = t.id and
        date_format(u.date, '%Y-%m') = date_format(t.date, '%Y-%m') and u.date <= t.date
) as calculated_column
from table1 t

(Change the table name table1 to suit your data)

Answer 2

In Oracle and MySQL 8+, you can use window functions. The corresponding date arithmetic varies, but here is the idea:

select t.*,
       (case when date = max(date) over (partition by to_char(date, 'YYYY-MM') and
                  id = 1
             then sum(amount) over (partition by to_char(date, 'YYYY-MM')
        end) as calculated_column
from t;

The outer case is simply to put the value on the appropriate row of the result set. The code would be simpler if all rows in the month had the same value.

Answer 3

Here is a solution for oracle. Since you did not gave the table name I named it my_table, change it to the real name

select
    t1.id, 
    t1.date,
    t1.amount,
    decode(t1.id, 1, sum(nvl(t2.amount, 0)), null) calculated_column
from my_table1 t1
left join my_table t2 
    on trunc(t2.date, 'month') = trunc(t1.date, 'month')
    and t1.id = 1
group by t1.id, t1.date, t1.amount

Answer 4

If your version supports window function (e.g. MySQL 8 upwards)

# MySQL 8+
select 
       t.*
     , sum(amount) over (partition by id, date_format(date, '%Y-%m-01') order by date) as calculated_column
from t
;

-- Oracle
select 
       t.*
     , sum(amount) over (partition by id, trunc(date, 'MM') order by date) as calculated_column
from t
;