MySQL Filter Between Date in Different Column

Something like this should work:

SELECT id_items,
    SUM(IF(MONTH(t.date) = 9, t.total, 0)) AS sept_total,
    SUM(IF(MONTH(t.date) = 10, t.total, 0)) AS oct_total
FROM transactions t
GROUP BY id_items

The idea is to group by id_items and then SUM() the aggregate totals conditionally depending on month of transaction. The above is fairly simplistic and assumes there are no transactions before 2020. If your data spans multiple years you’d need to account for that in your condition somewhere, e.g., WHERE YEAR(t.date) = 2020