Oracle 23c

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

CREATE TABLE employee_tb (
  employee_id INT,
  employee_type VARCHAR2(1)
);

INSERT INTO employee_tb (employee_id, employee_type)
VALUES
  (1, 'A'),
  (2, 'A'),
  (3, 'B'),
  (4, 'C'),
  (5, 'C'),
  (6, 'C');
6 rows affected

SELECT LISTAGG(employee_type || ': ' || employee_type_count, ', ') WITHIN GROUP (ORDER BY employee_type) AS result
FROM (
    SELECT employee_type, COUNT(*) AS employee_type_count
    FROM employee_tb
    GROUP BY employee_type
) subquery;


RESULT


A: 2, B: 1, C: 3