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"

Try `GROUP BY upper(concat(concat(concat(e2.First_name,' '),' '),e2.Last_Name))` instead. — jarlh, Apr 11 '22 at 08:52
[**ORA-00979**](https://docs.oracle.com/cd/B10501_01/server.920/a96525/e900.htm#1004903): **Cause**: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause. **Action**: Include in the GROUP BY clause all SELECT expressions that are not group function arguments. — astentx, Apr 11 '22 at 09:03
Does this answer your question? [ORA-00979 not a group by expression](https://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) — philipxy, Apr 11 '22 at 11:19
[How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [When is it justifiable to downvote a question?](https://meta.stackoverflow.com/q/252677/3404097) [ask] [Help] — philipxy, Apr 11 '22 at 11:24

score 1 · Answer 1 · answered Apr 11 '22 at 09:01

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

How do add (count)

1 Answers1