CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
ABHISHEK VELANKAR
ABHISHEK VELANKAR

Reputation: 3

Oracle Procedure not a group by function error 00979

i have written this procedure to select employees from employee table and corresponding sum of total quantity sold by purchases table. my procedure is created without any error but the block is not working

enter image description here

enter image description here

Upvotes: 0

Views: 120

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

Change the query in your procedure to

SELECT e.NAME, SUM(p.QTY)
  INTO emp_name, total_qty
  FROM PURCHASES p
  INNER JOIN EMPLOYEES e
    ON e.EID = p.EID
  WHERE p.EID = empno
  GROUP BY e.NAME

Basically, use WHERE instead of HAVING. If you're really dying to use HAVING you can use

SELECT p.EID, e.NAME, SUM(p.QTY)
  INTO emp_name, total_qty
  FROM PURCHASES p
  INNER JOIN EMPLOYEES e
    ON e.EID = p.EID
  GROUP BY p.EID, e.NAME
  HAVING p.EID = empno

To use HAVING the field it refers to must be in the results, and thus must be in the GROUP BY.

Best of luck.

Upvotes: 1

Related Questions