CODEWITHSUNDEEP
sqloracle-database
Shury
Shury

Reputation: 578

"Group by" expression

I have some problems trying to print department_id, department_name and the sum of salaries of each department and I can't figure out why. I get the error: 00979 - "not a GROUP BY expression"

enter image description here

SELECT d.department_id, d.department_name, SUM(e.salary)
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_id;

Upvotes: 0

Views: 77

Answers (3)

David Faber
David Faber

Reputation: 12485

Alternately, you can aggregate the department name as well using MIN() or MAX():

SELECT d.department_id, MAX(d.department_name) AS department_name
     , SUM(e.salary) AS department_salary
  FROM departments d INNER JOIN employees e
    ON d.department_id = e.department_id
 GROUP BY d.department_id;

Note that I updated your syntax from the old ANSI standard to the newer one. If you prefer the older syntax (as I do), then just use this:

SELECT d.department_id, MAX(d.department_name) AS department_name
     , SUM(e.salary) AS department_salary
  FROM departments d, employees e
 WHERE d.department_id = e.department_id
 GROUP BY d.department_id;

Upvotes: 1

FutbolFan
FutbolFan

Reputation: 13723

SELECT d.department_id, d.department_name, SUM(e.salary)
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

Just needed to add the department_name column in your group by clause.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Just add the name to the group by expression. Along the way, also fix the query to use explicit join syntax:

SELECT d.department_id, d.department_name, SUM(e.salary)
FROM departments d JOIN
     employees e
     ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

Although you didn't ask, I will point out that your version of the query does make sense and is ANSI-compliant (although most databases don't support this feature). You are aggregating by a primary key, so bringing in additional columns is allowed -- although Oracle does not support this feature.

Upvotes: 5

Related Questions