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
CLICK HERE to find out more related problems solutions.