MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE `transfers` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `id_account_destination` bigint(20) unsigned NOT NULL,
 `id_account_origin` bigint(20) unsigned NOT NULL,
 `amount` decimal(10, 2) signed NOT NULL DEFAULT 0,
 `created_at` datetime DEFAULT CURRENT_TIMESTAMP,

 PRIMARY KEY (`id`),
 KEY `id_account_destination` (`id_account_destination`),
 KEY `id_account_origin` (`id_account_origin`),
 KEY `created_at` (`created_at`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into transfers (id_account_destination, id_account_origin, amount)
values (1, 2, 100), 
       (1, 3,  50),
       (4, 1,  30);

Records: 3  Duplicates: 0  Warnings: 0


select * from transfers;


id
id_account_destination
id_account_origin
amount
created_at


1
1
2
100.00
2023-05-04 20:03:16

2
1
3
50.00
2023-05-04 20:03:16

3
4
1
30.00
2023-05-04 20:03:16


select x.id_account, 
    sum(x.income_amount) income_amount, 
    sum(x.outcome_amount) outcome_amount
from transfers t
cross join lateral (
    select t.id_account_destination, t.amount, 0 
    union all select t.id_account_origin, 0, t.amount
) x(id_account, income_amount, outcome_amount)
group by x.id_account


id_account
income_amount
outcome_amount


1
150.00
30.00

2
0.00
100.00

3
0.00
50.00

4
30.00
0.00

id	id_account_destination	id_account_origin	amount	created_at
1	1	2	100.00	2023-05-04 20:03:16
2	1	3	50.00	2023-05-04 20:03:16
3	4	1	30.00	2023-05-04 20:03:16