Search code examples
mysqlsqlmysql-logic

How to create SQL based on complex rule?

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)

enter image description here

Solution

  • 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)