ORACLE: not a GROUP BY expression

Question

I am trying to figure this out but I keep getting this error. I know it has something to do with the subqueries in the SELECT and HAVING clauses but I have no idea how to fix them. Any help would be greatly appreciated.

department table: https://gyazo.com/f9d782abd428acc7ec2e7d5d59befad7

employee table: https://gyazo.com/b2d7d792c0933b13d6fdd7166fffb8a6

expected output: https://gyazo.com/41f6a5626a5827acbe76e3c41287742d

EDIT: I figured out an alternative syntax! Thanks for all the help!

    CURSOR c_emp (p_total_emp NUMBER) IS
        SELECT department.DEPARTMENT_NAME                                             
             ,  MAX(employee.HIRE_DATE)                            
             , (SELECT COUNT(EMPLOYEE_ID)
                  FROM employee
                HAVING HIRE_DATE = MAX(HIRE_DATE))
             , ROUND(MAX(employee.SALARY))
             , ROUND(MAX(employee.SALARY) * 0.68)                   
             , (SELECT COUNT(EMPLOYEE_ID)
                  FROM employee
                HAVING SALARY > ROUND(MAX(employee.SALARY) * 0.68))
          FROM employee
         INNER JOIN department 
            ON employee.DEPARTMENT_ID = department.DEPARTMENT_ID
         GROUP BY 1 
         HAVING (SELECT COUNT(EMPLOYEE_ID) 
                  FROM employee
                HAVING SALARY > ROUND(MAX(employee.SALARY) * 0.68))  > p_total_emp              
        ORDER BY DEPARTMENT_NAME ASC
        ;

sample:

Answer 1

Hoping you're having a great week so far. :)

Just a heads up on the query, you are experiencing the error because you can't GROUP BY 1. You need to GROUP BY columns that you're not aggregating. (eg. department.DEPARTMENT_NAME) like the following:-

CURSOR c_emp (p_total_emp NUMBER) IS
        SELECT department.DEPARTMENT_NAME                                             
             ,  MAX(employee.HIRE_DATE)                            
             , (SELECT COUNT(EMPLOYEE_ID)
                  FROM employee
                HAVING HIRE_DATE = MAX(HIRE_DATE))
             , ROUND(MAX(employee.SALARY))
             , ROUND(MAX(employee.SALARY) * 0.68)                   
             , (SELECT COUNT(EMPLOYEE_ID)
                  FROM employee
                HAVING SALARY > ROUND(MAX(employee.SALARY) * 0.68))
          FROM employee
         INNER JOIN department 
            ON employee.DEPARTMENT_ID = department.DEPARTMENT_ID
         GROUP BY department.DEPARTMENT_NAME 
         HAVING (SELECT COUNT(EMPLOYEE_ID) 
                  FROM employee
                HAVING SALARY > ROUND(MAX(employee.SALARY) * 0.68))  > p_total_emp              
        ORDER BY DEPARTMENT_NAME ASC
        ;