Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    15

    Unanswered: MAX vs COUNT - subselect

    I have a table where is recorded each employee carreer in my enterprise.

    This is my table:

    employee_id | date | function


    The last date I have for each employee corresponds to the present function of that employee in my enterprise.

    How can I know how many employees I have in each function at the present time?

    I tried this one without success :


    select id_function, count(id_function) from
    (select id_employee, max(id_function) from carreer group by id_employee) group by id_function;


    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I wish I worked for you - I would love to only have one function in my enterprise

    You would be looking to find the last date for each employee in one query and then match this last date to your table to find the last function your employees have been essigned.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    match? like with a join? we don' need no steenkin joins!
    Code:
    select function
         , count(*) as employees
      from (
           select function
                , employee_id
             from carreer as T
            where date
                = ( select max(date)
                      from carreer
                     where employee_id = T.employee_id )  
           ) as latest
    group
        by function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    match? like with a join?
    Or match to corrolated subQ result

    Is that your latest NZDF mission - eliminate joins from RFH responses?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2005
    Posts
    15

    max count

    Maybe I didn't explain myself quite well. What I'd like to know is:

    How many employees I have in each level of my organization chart (function) at the present date.
    So what I need is to count how many employees I have in each "function" of this select -

    Select employee_id, max(funtion) from carreer group by employee_id

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you at least try the query i suggested? what was the result?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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