Oracle Procedure not a group by function error 00979

Question

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

Answer 1

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.