0

how to resolve error ORA-00979: not a GROUP BY expressionenter image description here

    DECLARE
        CURSOR TAMPILDATA IS
        select to_char(hire_date,'YYYY') as thn, count(to_char(hire_date,'YYYY'))as jumlah, job_title
        from employees a,jobs b
where a.job_id=b.job_id
group by to_char(hire_date,'YYYY') order by to_char(hire_date,'YYYY');
    BEGIN
        FOR VARIABLE_X IN TAMPILDATA LOOP
            DBMS_OUTPUT.PUT_LINE (VARIABLE_X.thn||' : ' ||VARIABLE_X.JUMLAH||' Orang'); 
        END LOOP;
    END;
Yonathan Rizky
  • 59
  • 1
  • 6
  • 1
    you must group by job_title too or use an aggregate function on it – Turo Jan 07 '18 at 10:56
  • Every column in the result set must be either a column that you are grouping by, or an aggregate. "to_char(hire_date,'YYYY')" is the hiring_year You are grouping by hiring_year Then you are counting hiring_year and this will always be "1" because there must be exactly one hiring_year in each group Then you are listing job_title which is neither an aggregate or a grouping column. There will be many job_titles per hiring_year so the database cannot give you just one job_title for each hiring_year. The solution is to work out exactly what you want to group and what you want to count. – Ron Ballard Jan 07 '18 at 11:04
  • For future quesions, please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer –  Jan 07 '18 at 13:44

0 Answers0