Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2011
    Posts
    14

    Unanswered: SQL: Include Name, ID that are not group by elements?

    For each employee whose job is performed by more than 1
    Last edited by vegan_toronto; 02-22-12 at 15:40. Reason: NICE

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post CREATE TABLE & INSERT statements so we have table & data to write desired SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    178 Grant SA_REP 2
    103 Hunold IT_PROG 2
    107 Lorentz IT_PROG 2
    143 Matos ST_CLERK 3
    141 Rajs ST_CLERK 3
    176 Taylor SA_REP 2
    144 Vargas ST_CLERK 3
    Looking into your data
    IT_PROG: 2 employees (103, Hunold) and (107, Lorentz)
    SA_REP: 2 employees (178, Grant) and (176, Taylor)
    ST_CLERK: 3 employees (143, Matos), (141, Rajs) and (144, Vargas)

    So, I thought that numbers which you showed as "COUNT(E.JOB_ID)-1" might be "COUNT(E.JOB_ID)".
    and "HAVING (COUNT(E.JOB_ID)-1)>1" might be "HAVING COUNT(E.JOB_ID) > 1".

  4. #4
    Join Date
    Oct 2011
    Posts
    14
    OR...

    SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID
    FROM EMPLOYEES
    WHERE JOB_ID IN
    (SELECT JOB_ID FROM EMPLOYEES WHERE COUNT(JOB_ID) >1);

    =====
    error: group function not allowed here

    but this is what im looking for, i think, the number of employees more than 1 in each JOB_ID...

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  with dept_cnt as (select deptno, count(deptno) from emp group by deptno having count(deptno) > 1)
      2  select ename, dept_cnt.deptno
      3  from emp  ee, dept_cnt
      4* where ee.deptno = dept_cnt.deptno
    SQL> /
    
    ENAME	       DEPTNO
    ---------- ----------
    ALLEN		   30
    WARD		   30
    JONES		   20
    MARTIN		   30
    BLAKE		   30
    CLARK		   10
    SCOTT		   20
    KING		   10
    TURNER		   30
    ADAMS		   20
    JAMES		   30
    
    ENAME	       DEPTNO
    ---------- ----------
    FORD		   20
    MILLER		   10
    SALAM		   20
    KARIN		   20
    RAFIQ		   20
    JABBAR		   20
    RAHIM		   20
    
    18 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Oct 2011
    Posts
    14
    thank you all, but still cant make it work.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    my posted SQL does what you desire; just against different table
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Oct 2011
    Posts
    14
    thank you, anacedent! but i also need to display the number of deptno i.e.

    ENAME DEPTNO
    ---------- ----------
    ALLEN 30 5
    WARD 30 5
    JONES 20 3
    MARTIN 30 5
    BLAKE 30 5
    CLARK 10 2
    SCOTT 20 3
    KING 10 2
    TURNER 30 5
    ADAMS 20 3
    JAMES 30 5

    (for the sake of simplicity i skipped second page)

  9. #9
    Join Date
    Oct 2011
    Posts
    14
    but your code is definitely of a great help.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >thank you, anacedent! but i also need to display the number of deptno i.e.
    the COUNT(DEPTNO) exists, so include it to the SELECTed columns
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Oct 2011
    Posts
    14
    thanks but then im back to square one. then it complains that all select items other than count i.e. employee id, name are not group-group function. if i group by them then count will show 1 for all rows.

    my problem from the very beginning is that i can either show correctly id, name, job_id but not the count or i can show count but not the other columns.

    thank you all again, i appreciate your time and effort.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it is impossible to say what you are doing wrong, since we can not actually see what you do.

    If you provided tables (CREATE TABLE) & data (INSERT), then I could produce desired SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Oct 2011
    Posts
    14
    i sent you a pm with all the data. 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
  •