How do add (count)

Question

I would like to add a count(e.employee_id), and group it by manager_id so the result I get is the number of employees

SELECT upper(concat(concat(concat(e2.First_name,' '),' '),e2.Last_Name)) "JEFE",
       e.employee_id
FROM employees e join employees e2 
  on (e.manager_id = e2.employee_id);

The result here is:

Name: Mike, Employee_Id: 101
Name: Mike, Employee_Id: 102
Name: Mike, Employee_Id: 103
Name: Mike, Employee_Id: 104
Name: Jason, Employee_Id: 201
Name: Jason, Employee_Id: 202
Name: Jason, Employee_Id: 203
Name: Jason, Employee_Id: 204

I want the result to be:

Name: Mike, Employee_Id: 4
Name: Jason, Employee_Id: 4

I tried doing this:

SELECT upper(concat(concat(concat(e2.First_name,' '),' '),e2.Last_Name)) "JEFE",
       count(e.employee_id)
FROM employees e join employees e2 
  on (e.manager_id = e2.employee_id)
group by e.manager_id;

But the SQL said:

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"

Answer 1

Either add the name to the GROUP BY clause or wrap the name in an aggregation function.

You can also use a hierarchical query rather than a (slower) self-join:

SELECT MAX(UPPER(PRIOR First_name || '  ' || PRIOR Last_Name)) AS manager_name,
       COUNT(employee_id),
       manager_id
FROM   employees
WHERE  LEVEL = 2
CONNECT BY
       PRIOR employee_id = manager_id
GROUP BY
       manager_id;

or:

SELECT UPPER(PRIOR First_name || '  ' || PRIOR Last_Name) AS manager_name,
       COUNT(employee_id),
       manager_id
FROM   employees
WHERE  LEVEL = 2
CONNECT BY
       PRIOR employee_id = manager_id
GROUP BY
       PRIOR first_name,
       PRIOR last_name,
       manager_id;

Which, for the sample data:

CREATE TABLE employees (employee_id, manager_id, first_name, last_name) AS
SELECT 1, NULL, 'Alice', 'Abbot' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chase' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Doris' FROM DUAL UNION ALL
SELECT 5, 3, 'Emily', 'Evans' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Frank' FROM DUAL UNION ALL
SELECT 7, 6, 'Gemma', 'Grace' FROM DUAL;

Both outputs:

MANAGER_NAME	COUNT(EMPLOYEE_ID)	MANAGER_ID
BERYL BARON	1	2
CAROL CHASE	2	3
FIONA FRANK	1	6
ALICE ABBOT	2	1

db<>fiddle here