Results 1 to 12 of 12

Thread: max(count(sum))

  1. #1
    Join Date
    May 2008
    Posts
    6

    Unanswered: max(count(sum))

    Hi guys
    i need some help..

    i have a table

    WORK

    with fields

    EMPLOYEE, PROJECT, HOURS

    with primary key (employee,project)

    and i want to select the employee who has worked the most hours in the sum of the projects he had participate

    i use oracle 9i

    EXAMPLE

    name1 pro1 10
    name2 pro1 10
    name2 pro2 10
    name2 pro3 10
    name3 pro2 10
    name3 pro3 40

    i want to return name3

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What did you try so far?
    Did you get any errors? Wrong result?

  3. #3
    Join Date
    May 2008
    Posts
    6
    select employee, count(employee)
    from work
    group by employee
    having count(employee) =
    ( select max(cnt) from
    ( select employee, count(employee cnt
    from work
    group by employee
    )
    );

    this works and it shows the employee which participate to the most projects an the number of the projects he praticipates
    but i dont know how i can change this to get the the employee who works the most hours in all the projects he participates

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your first step should be a query which produces the sum of hours for each employee

    once you've figured this out, please show your query and then show the results that it produced

    then we'll show you how to modify it for the next stage
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2008
    Posts
    6
    Quote Originally Posted by r937
    your first step should be a query which produces the sum of hours for each employee

    once you've figured this out, please show your query and then show the results that it produced

    then we'll show you how to modify it for the next stage
    i dont have oracle in my pc and i cant access it until tommorow
    but i need this until tommorow morning for my univercity

    So i cant see the result but i imagine the query u told me to make
    is like that

    select employee, count(employee),sum(hours)
    from work
    group by employee;

    i dont know if this is right or wrong....

  6. #6
    Join Date
    May 2006
    Posts
    42

    Lightbulb

    hi i m a bit new to this stuff sql

    this should work

    Code:
    select employee from work where 
    hours in ( select max(ma) from (select project pr,max(hours) ma from
    work group by project));
    @pantelis
    i ran ur query it returns
    Code:
    EMPLOYEE             COUNT(EMPLOYEE) SUM(HOURS)
    -------------------- --------------- ----------
    name1                              1         10
    name2                              3         30
    name3                              2         50

  7. #7
    Join Date
    May 2008
    Posts
    6
    COuld u explain me plz what is the 'ma'?

  8. #8
    Join Date
    May 2008
    Posts
    6
    Quote Originally Posted by iinfi
    hi i m a bit new to this stuff sql

    this should work

    EMPLOYEE COUNT(EMPLOYEE) SUM(HOURS)
    -------------------- --------------- ----------
    name1 1 10
    name2 3 30
    name3 2 50
    [/code]
    from this i want to return only

    name 3

  9. #9
    Join Date
    May 2006
    Posts
    42
    oops sorry

    thats jus an alias for "max(hours)"
    in the subquery "(select project pr,max(hours) ma from
    work group by project)"

    added:
    select employee from work where
    hours in ( select max(ma) from (select project pr,max(hours) ma from
    work group by project));

    this one is correct. the one i posted earlier

  10. #10
    Join Date
    May 2008
    Posts
    6
    OK ty for your anwser and your explanation
    i think ill use ur query

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by pantelis
    i dont have oracle in my pc and i cant access it until tommorow
    How convenient!
    Quote Originally Posted by pantelis
    but i need this until tommorow morning for my univercity
    But of course you do!

    Obviously you have :
    1. personal computer
    2. access to internet

    What prevents you to download and install Oracle 10g Express Edition database and write queries by yourself? That would help you, actually, LEARN something. Now, now you have a query but can't explain it (you don't even know what a column alias is!). I'm afraid that "tomorrow morning" you might pass the "test", but - in the long run - you're in deep trouble.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    pantelis,
    Do you expect to have others doing all your homework assignments for this course for the whole semester?

    Thanks!
    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.

Posting Permissions

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