Hi,
I have 2 tables for example EMP and DEPT

EMP DEPT
------- --------
empno deptno
empname dept_name
deptno
job

i want result as all the department names and corresponding
employees count for the given job title.

deptname count
-------- --------
sales 100
accounting 20
research 10

I am not good at SQL, please help me!

Thanks

If you have tried a query and found it throwing errors, please post the same ...

Cheers
Sathyaram

I tried

O.K. let me give my actual problem where i am working on.

i have the following tables

CFGHDRLOG CFGSERVICE
-------------- ------------------
MSGID SERVICEID
LOGONID SERVICE_NAME
CHANNELID
SERVICEID


I tried the following query
SELECT SERVICEID, COUNT(*) FROM DB2A.CFGHDRLOG WHERE CHANNELID=3482 GROUP BY SERVICEID

i got the follwing result

SERVICEID 2
--------------- -------
34 2
27 6
20 8

now i want display SERVICE_NAME instead of SERVICEID, which is in CFGSERVICE table?

i coud not figure it out!!!

SELECT LOG.SERVICEID, SVC.SERVICE_NAME, COUNT(*) FROM DB2A.CFGHDRLOG LOG, DB2A.CFGSERVICE SVC WHERE LOG.CHANNELID=3482 AND LOG.SERVICEID = SVC.SERVICEID GROUP BY LOG.SERVICEID

I tried it i am getting the following error

SQL0119N An expression starting with "SERVICE_NAME" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803

Yes, that's my mistake. The error message is sort of self-explanatory, isn't it? Add "svc.service_name" to the GROUP BY clause.

i did this way as u said

db2 => SELECT LOG.SERVICEID, SVC.SERVICE_NAME, COUNT(*) FROM DB2A.CFGHDRLOG LOG, DB2A.CFGSERVICE SVC WHERE LOG.CHANNELID=3482 AND LOG.SERVICEID = SVC.SERVICEID GROUP BY SVC.SERVICE_NAME

SQL0119N An expression starting with "SERVICEID" specified in a SELECT clause,HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803

i got the same kind of error!!

He said "add", you replaced... use both in the GROUP BY.

Try this

SELECT SVC.SERVICE_NAME, LOG.SERVICEID,COUNT(*) FROM DB2A.CFGHDRLOG LOG, DB2A.CFGSERVICE SVC WHERE LOG.CHANNELID=3482 AND LOG.SERVICEID = SVC.SERVICEID GROUP BY SVC.SERVICE_NAME, LOG.SERVICEID

Cheers

It's working fine.

Thank you very much!