MySQL 8.0

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

CREATE TABLE sample_table (
  job varchar(10),
  job_state varchar(1),
  start_time time,
  end_time time 
);
INSERT INTO sample_table (job, job_state, start_time, end_time)
VALUES
('Job1', 'R', '01:00:00', '03:00:00'),
('Job2', 'R', '02:00:00', '04:00:00'),
('Job3', 'R', '01:00:00', '05:00:00'),
('Job4', 'R', '05:00:00', '09:00:00'),
('Job5', 'R', '01:00:00', '05:00:00'),
('Job6', 'R', '02:00:00', '06:00:00'),
('Job7', 'R', '01:00:00', '04:00:00')
SELECT * FROM sample_table


job
job_state
start_time
end_time


Job1
R
01:00:00
03:00:00

Job2
R
02:00:00
04:00:00

Job3
R
01:00:00
05:00:00

Job4
R
05:00:00
09:00:00

Job5
R
01:00:00
05:00:00

Job6
R
02:00:00
06:00:00

Job7
R
01:00:00
04:00:00


WITH cte AS (SELECT
  1,
  GROUP_CONCAT(job SEPARATOR ', ') as jobs,
  COUNT(job) AS job_count
FROM
  sample_table 
WHERE
  start_time >= '1:00'
  AND
  end_time <= '4:00'
GROUP BY
  1
) SELECT jobs AS "Jobs", job_count AS "Job Count" FROM cte


Jobs
Job Count


Job1, Job2, Job7
3


SELECT jobs AS "Jobs", job_count AS "Job Count"
FROM
(
SELECT
  1,
  GROUP_CONCAT(job SEPARATOR ', ') as jobs,
  COUNT(job) AS job_count
FROM
  sample_table 
WHERE
  start_time >= '1:00'
  AND
  end_time <= '4:00'
GROUP BY
  1
) a


Jobs
Job Count


Job1, Job2, Job7
3


SELECT
  job AS jobs,
  COUNT(job) AS job_count
FROM
  sample_table 
WHERE
  start_time >= '1:00'
  AND
  end_time <= '4:00'
GROUP BY
  job


jobs
job_count


Job1
1

Job2
1

Job7
1

job	job_state	start_time	end_time
Job1	R	01:00:00	03:00:00
Job2	R	02:00:00	04:00:00
Job3	R	01:00:00	05:00:00
Job4	R	05:00:00	09:00:00
Job5	R	01:00:00	05:00:00
Job6	R	02:00:00	06:00:00
Job7	R	01:00:00	04:00:00