MAX vs COUNT - subselect

riojan · #1 (**permalink**) 04-24-06, 05:26

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.

pootle flump · #2 (**permalink**) 04-24-06, 05:48

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

r937 · #3 (**permalink**) 04-24-06, 06:56

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

pootle flump · #4 (**permalink**) 04-24-06, 07:34

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?

riojan · #5 (**permalink**) 04-26-06, 11:14

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

r937 · #6 (**permalink**) 04-26-06, 11:32

did you at least try the query i suggested? what was the result?

Internet Services

Web Development

Digital Marketing

Consumer Tech