stackuser stackuser - 2 months ago 6
SQL Question

why do I get syntax error of not a group by expression in below query?

I have a schema of

emp
table as
(empno , ename , hiredate ,sal,job)
and I have the query as :

Find the total annual salary to distribute job wise in the year 81.

I wrote the below query :

select job, sum(Sal*12)
from emp
group by job
having (extract(year from hiredate)) = '1981' ;


This query is giving output as "not a group by expression " ,please guide me in identifying the problem here .

Answer

This error is coming as the clause used in having is not part of group by. Rewrite the query as select job, sum(Sal * 12) from SO_EMP group by job, (extract(year from hiredate)) having(extract(year from hiredate)) = '1981';