Results 1 to 9 of 9
  1. #1
    Join Date
    May 2005
    Posts
    37

    Unanswered: Resolved

    Hi guys i am abit stuck on some functions and need some help

    I like to do a query function

    were from my emp table i like to find out from the job column how many MANAGERS there are and list them as

    MANAGER
    .............
    3

    not like

    SQL> select job, INSTR(job,'MANAGER')
    from emp
    where job = 'MANAGER' 2 3 ;

    JOB INSTR(JOB,'MANAGER')
    --------- --------------------
    MANAGER 1
    MANAGER 1
    MANAGER 1

    Also I like to find out how many diffeernt jobs there are from the job colum
    and display them like

    job_type
    ----------
    5
    Last edited by Khan05; 02-05-07 at 17:24. Reason: Resolved

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you have to count managers, why don't you count them?
    Code:
    SELECT COUNT(*) FROM emp
    WHERE job = 'MANAGER';
    For number of different jobs, use COUNT(*) function along with the DISTINCT keyword:
    Code:
    SELECT COUNT(DISTINCT job) FROM emp;

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    or both together...

    select count(distinct job) jobs, sum(decode(job,'MANAGER',1,0)) managers
    from emp;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    May 2005
    Posts
    37
    Hi beilstwh

    Just wondering


    what is the use of the 1.0)) after the MANAGER. So i can understand it more


    select sum(decode(job,'MANAGER',1,0)) managers
    from emp;

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what is the use of the 1.0)) after the MANAGER.
    Let me get this straight.
    You found your to this forum.
    You know enough to compose simple SQL queries.
    However it seems that you don't know how to use Google
    and/or are either incapable or unwilling to read the SQL Referernce Manual to learn about the DECODE function.
    Please explain why you think anyone should bother to answer questions you could answer yourself if you'd make the SLIGHTEST effort.
    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
    May 2005
    Posts
    37
    Yooo anacedent

    Looks like you are having a bad day. Take a Chil Pill or sumthing. You didnt do anything so theres no point braging, Dnt hate the player Just hate the game.

    Thanks To Littlefoot and beilstwh.

    I would read upon on that DECODE

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The decode works as follows

    sum(decode(job,'MANAGER',1,0)

    if job is equal to 'MANAGER' then return 1, if it is not then return 0.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    May 2005
    Posts
    37

    Resolved

    Thanks beilstwh

    I understand it much better.


  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your welcome, anacedent might have a sharp tongue, but she is very knowledgeable and insightful.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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