MySQL 5.7

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

create table t_system_log
(
system_name varchar(10),
subsystem_filename varchar(100),
file_load_start_time timestamp null,
file_load_end_time timestamp null
)
insert into t_system_log
values('Alpha','A1 transactiontxt','2022-06-19 08:00:00','2022-06-19 08:00:02'),
('Alpha','A2 userscsv','2022-06-19 08:00:02','2022-06-19 08:00:05'),
('Alpha','A2 employeescsv','2022-06-19 08:00:05','2022-06-19 08:00:08'),
('Alpha','A1 managerscsv','2022-06-19 08:00:08','2022-06-19 08:00:16'),
('Alpha','A3 customerscsv','2022-06-19 08:00:01','2022-06-19 08:00:04'),
('Gamma','A1 transactiontxt','2022-06-19 10:00:48','2022-06-19 10:00:53'),
('Gamma','A2 userscsv','2022-06-19 10:00:53','2022-06-19 10:00:54'),
('Gamma','A2 employeescsv','2022-06-19 10:00:27','2022-06-19 10:00:30'),
('Gamma','A1 managerscsv','2022-06-19 10:00:11','2022-06-19 10:00:17'),
('Gamma','A3 customerscsv','2022-06-19 10:00:13','2022-06-19 10:00:14');
select * from t_system_log;


system_name
subsystem_filename
file_load_start_time
file_load_end_time


Alpha
A1 transactiontxt
2022-06-19 08:00:00
2022-06-19 08:00:02

Alpha
A2 userscsv
2022-06-19 08:00:02
2022-06-19 08:00:05

Alpha
A2 employeescsv
2022-06-19 08:00:05
2022-06-19 08:00:08

Alpha
A1 managerscsv
2022-06-19 08:00:08
2022-06-19 08:00:16

Alpha
A3 customerscsv
2022-06-19 08:00:01
2022-06-19 08:00:04

Gamma
A1 transactiontxt
2022-06-19 10:00:48
2022-06-19 10:00:53

Gamma
A2 userscsv
2022-06-19 10:00:53
2022-06-19 10:00:54

Gamma
A2 employeescsv
2022-06-19 10:00:27
2022-06-19 10:00:30

Gamma
A1 managerscsv
2022-06-19 10:00:11
2022-06-19 10:00:17

Gamma
A3 customerscsv
2022-06-19 10:00:13
2022-06-19 10:00:14


SELECT
	system_name AS "System", 
	min(file_load_start_time) AS "Overall System Load Start Time", 
	max(file_load_end_time) AS "Overall System Load End Time",
	(
	SELECT
		sec_to_time(sum(timestampdiff(SECOND, tsl_a1.file_load_start_time, tsl_a1.file_load_end_time)))
	FROM
		t_system_log tsl_a1
	WHERE
		tsl_a1.system_name = tsl.system_name
		AND LEFT(tsl_a1.subsystem_filename,
		2) = 'A1') AS "A1 Time Taken",
	(
	SELECT
		sec_to_time(sum(timestampdiff(SECOND, tsl_a2.file_load_start_time, tsl_a2.file_load_end_time)))
	FROM
		t_system_log tsl_a2
	WHERE
		tsl_a2.system_name = tsl.system_name
		AND LEFT(tsl_a2.subsystem_filename,
		2) = 'A2') AS "A2 Time Taken",
	(
	SELECT
		sec_to_time(sum(timestampdiff(SECOND, tsl_a3.file_load_start_time, tsl_a3.file_load_end_time)))
	FROM
		t_system_log tsl_a3
	WHERE
		tsl_a3.system_name = tsl.system_name
		AND LEFT(tsl_a3.subsystem_filename,
		2) = 'A3') AS "A3 Time Taken"
FROM
	t_system_log tsl
GROUP BY
	system_name;


System
Overall System Load Start Time
Overall System Load End Time
A1 Time Taken
A2 Time Taken
A3 Time Taken


Alpha
2022-06-19 08:00:00
2022-06-19 08:00:16
00:00:10
00:00:06
00:00:03

Gamma
2022-06-19 10:00:11
2022-06-19 10:00:54
00:00:11
00:00:04
00:00:01

system_name	subsystem_filename	file_load_start_time	file_load_end_time
Alpha	A1 transactiontxt	2022-06-19 08:00:00	2022-06-19 08:00:02
Alpha	A2 userscsv	2022-06-19 08:00:02	2022-06-19 08:00:05
Alpha	A2 employeescsv	2022-06-19 08:00:05	2022-06-19 08:00:08
Alpha	A1 managerscsv	2022-06-19 08:00:08	2022-06-19 08:00:16
Alpha	A3 customerscsv	2022-06-19 08:00:01	2022-06-19 08:00:04
Gamma	A1 transactiontxt	2022-06-19 10:00:48	2022-06-19 10:00:53
Gamma	A2 userscsv	2022-06-19 10:00:53	2022-06-19 10:00:54
Gamma	A2 employeescsv	2022-06-19 10:00:27	2022-06-19 10:00:30
Gamma	A1 managerscsv	2022-06-19 10:00:11	2022-06-19 10:00:17
Gamma	A3 customerscsv	2022-06-19 10:00:13	2022-06-19 10:00:14