MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();


VERSION()


8.0.30


DROP TABLE IF EXISTS `transactions`;

CREATE TABLE IF NOT EXISTS `transactions` (
  `client_acount_id` CHAR(36),
  `amount` DECIMAL(10, 2),
  `created_at` DATETIME
);

INSERT INTO `transactions`
  (`client_acount_id`, `amount`, `created_at`)
VALUES
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2021-01-03 02:44:00'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2021-01-08 12:22:48'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-08-03 16:03:38'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-08-30 10:08:57'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-09-30 10:09:43'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-09-30 10:09:43'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-01 10:37:13'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-01 12:34:07'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-02 12:48:54'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-02 14:53:34'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-03 12:56:40'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-03 18:27:28'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-05 10:42:42'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-05 10:42:48'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-05 16:31:00'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-06 12:11:08'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-10-30 09:10:13'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-03-31 05:00:22'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-03-01 00:00:39'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-02-02 04:12:37'),
  ('9d2bb89c-372d-11ea-827a-16ec3745526d', 29.99, '2020-02-29 18:59:11');

Records: 21  Duplicates: 0  Warnings: 0


SELECT
  `der`.`year_month`,
  SUM(`der`.`amount`) `amount`
FROM (
  SELECT
    IF(
      DAY(`created_at`) > 5 /* Start pay day: 6 */,
      DATE_FORMAT(`created_at`, '%Y-%m'),
      DATE_FORMAT(DATE_ADD(`created_at`, INTERVAL -1 MONTH), '%Y-%m')
    ) `year_month`,
    `amount`
  FROM
    `transactions`
  WHERE
    `client_acount_id` = '9d2bb89c-372d-11ea-827a-16ec3745526d'
) `der`
GROUP BY
  `der`.`year_month`
ORDER BY
  `der`.`year_month` DESC;


year_month
amount


2021-01
29.99

2020-12
29.99

2020-10
59.98

2020-09
329.89

2020-08
29.99

2020-07
29.99

2020-03
29.99

2020-02
59.98

2020-01
29.99
year_month	amount
2021-01	29.99
2020-12	29.99
2020-10	59.98
2020-09	329.89
2020-08	29.99
2020-07	29.99
2020-03	29.99
2020-02	59.98
2020-01	29.99