I WANT TO KNOW WHY FUN LIKE SUM IS NOT ALLOWED IN GROUP BY

[gvs]

HELLO ,IAM NEW TO DB2 ,CAN ANY ONE GIVE ME EQUALENT QUERY FOR BELOW QUERIES IN DB2
SELECT YEAR(HIREDATE) RECUITEDYEAR
FROM EMPLOYEE
GROUP BY YEAR(HIREDATE);
IT IS GIVING ERROR.I I WANT TO KNOW DB2 NOT ALLOWING FUNCTIONS
IN GROUP BY CLAUSE. AND ALSO


SELECT SALARY,(SELECT MAX(SALARY) FROM EMPLOYEE;

[Anuj Dhawan]

TURN OFF your CAPS.

What do you ask? It's not clear from your description what you are trying to do? Do yo want to know," can functions be used with GROUP BY clause in DB2?" If yes may be this link can help you a bit; Else do a better job in explaining your query.

[Natarajan]

Anuj provided very good source to learn about GROUP BY clause in DB2.
Please go thru that page.

As per what i understand from your first query.. you want to display the YEAR part of HIREDATE, without duplicates... Is my understanding is correct?

[gvs]

Thanks for the reply.In my first query i want to display the years in which recruitment had taken place.As you suggested i can use distinct to eliminate duplicate.But if we want to
count the number of recruitments happened in the year how to do it in db2.In oracle
i can write query as
select to_char(hiredate,'yyyy'),count(*)
from emp
group by to_char(hiredate,'yyyy');

In my two query my desired output is something like
salary max(salary)
1000 30000
2000 30000
2541 30000
for that i need query in db2,
in oracle select salary,(select max(salary) from employee)
from employee;
but in db2 it is giving error like "max" is invalid.

[Natarajan]

Following query working... please check.

Code: Select all

SELECT YEAR(HIREDATE), COUNT(*)
FROM EMP
GROUP BY YEAR(HIREDATE)