Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    54

    Unanswered: query help please!!

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: query help please!!

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

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2004
    Posts
    54
    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!!!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by manth
    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

  5. #5
    Join Date
    Mar 2004
    Posts
    54
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by manth
    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.

  7. #7
    Join Date
    Mar 2004
    Posts
    54
    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!!

  8. #8
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by manth
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  9. #9
    Join Date
    Feb 2004
    Posts
    9
    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

  10. #10
    Join Date
    Mar 2004
    Posts
    54

    Thumbs up

    It's working fine.

    Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •