Join in SQL query gives ORA-00937 error

Question

      DECLARE

    v_department_name VARCHAR(50);
    v_department_manager VARCHAR(50);
    v_totalsalary NUMBER(5);

     BEGIN

  SELECT departments.department_name, concat(employees.first_name, employees.last_name), sum(employees.salary) 
  INTO v_department_name, v_department_manager, v_totalsalary 
  FROM employees LEFT JOIN departments ON employees.department_id =departments.department_id  WHERE  departments.department_id = 100;

   DBMS_OUTPUT.PUT_LINE ('Department Name is: ' || v_department_name || 'And    Department Manager is: ' || v_department_manager || 'Total Amount of Salary is: ' || v_totalsalary );

   END;

When trying to display a department_name, department manager (first_name, last_name), and a total amount of salary earned by employees of department id 100, I get this:

ORA-00937: not a single-group group function

What is causing that error?

Table employee(employee_id,first_name,last_name,department_id,manager_id,salary)

Table department(department_id,department_name,manager_id,location_id)

Someone else posted another question about the same homework assignment (with different errors in the code). I explained how to approach this problem there. https://stackoverflow.com/questions/44709714/pl-sql-sql-statement-ignored-and-missing-right-parenthesis/44709768?noredirect=1#comment76404070_44709768 — , Jun 23 '17 at 00:02
You changed your question in your comments. Please think about what you want to ask before writing the question. — Lorenz Meyer, Jun 23 '17 at 05:03
Possible duplicate of [pl/sql SQL Statement ignored and missing right parenthesis](https://stackoverflow.com/questions/44709714/pl-sql-sql-statement-ignored-and-missing-right-parenthesis) — Lorenz Meyer, Jun 23 '17 at 05:04
https://stackoverflow.com/search?q=%5Boracle%5D+not+a+single-group+group+function — , Jun 23 '17 at 05:36

Paul Maxwell · Accepted Answer · 2017-06-23T23:14:45.707

ORA-00937: Simply means you require GROUP BY clause, and in that clause repeat each column of the select clause that does NOT use an aggregate function such as SUM/MIN/MAX etc.

    SELECT
       departments.department_name
     , LISTAGG (employees.first_name || employees.last_name, '; ' )
           WITHIN GROUP (ORDER BY employees.last_name,employees.last_name) employee_names
     , SUM(employees.salary)
    FROM employees
    INNER JOIN departments ON employees.department_id = departments.department_id
    WHERE departments.department_id = 100
    GROUP BY
       departments.department_name
    ;

Note I am using LISTAGG() but you don't have to use that. If your 3 columns need to be Department, Department Manager and (sum of salary), then you need to choose the correct field from the department table for Manager and then include that column in the select and group by clauses.

score 1 · Answer 2 · answered Jun 23 '17 at 05:26

WITH temp AS (
    SELECT d.department_name, concat(e.first_name, e.last_name) AS emp_name, 
        sum(e.salary) AS salary_sum
    FROM employees e LEFT JOIN departments d ON e.department_id =d.department_id  
    WHERE d.department_id = 100
    GROUP BY d.department_name, concat(e.first_name, e.last_name)  )
SELECT t.department_name, t.emp_name, t.salary_sum
INTO v_department_name, v_department_manager, v_totalsalary 
FROM temp

This query uses common table expressions to group the data together to keep everything more coherent. You are getting the error because you are missing the GROUP BY d.department_name, concat(e.first_name, e.last_name) line in your original query. SUM() is an aggregate function, and can only be used in conjunction with GROUP BY.

Join in SQL query gives ORA-00937 error

2 Answers2