Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: joining a table to itself?

    Hi all-

    asomewhat novice question.

    i have a table like so:

    employee_data
    ------------------
    name
    task
    hours

    a single employee may appear a few times if they perform multiple tasks, and will have separate rows for the hours worked on each task.

    what i want to do is get a ratio of two particular tasks, and dump it into an aggregating function.

    for instance, if each of 3 individuals (jack, jill, john) does both of two types of task (read, write) for varying hours each- i want to calculate the average of each person's ratio of 'read' to 'study'. anyone have a clue how to achieve this?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    On the surface my initial conclusion is that you have not been paying attention in class & failed to do the reading assignment.
    Show us what you've already tried & why it was not satisfactory.

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    Originally posted by anacedent
    On the surface my initial conclusion is that you have not been paying attention in class & failed to do the reading assignment.
    Show us what you've already tried & why it was not satisfactory.
    Originally posted by anacedent
    On the surface my initial conclusion is that you have not been paying attention in class & failed to do the reading assignment.
    Show us what you've already tried & why it was not satisfactory.
    ha! no class alas, just the internet.

    what i believe will get me there is a query that return the table joined to itself and looks something like:

    name task1 hours1 task2 hours2
    ------- ------- -------- ------ -------
    jack read 2 write 2
    jill read 1 write 3
    john read 2 write 4

    from there it seems trivial (select avg(hours1/hours2) from ...

    so mostly i'm unclear on how i can efficiently merge each of the two rows that will return from a query like select task, hours from employee_data...
    Last edited by earlbradley; 12-30-03 at 22:09.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select a, b, sum(x)
    from table
    group by a, b;

    Will Return -

    empA taskA 2
    empA taskB 4
    empB taskA 3
    empB taskB 2
    ..
    etc.
    Last edited by r123456; 12-30-03 at 22:03.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Dec 2003
    Posts
    14
    got it. i ended up requiring a self join.

    thanks.
    Last edited by earlbradley; 12-30-03 at 22:42.

Posting Permissions

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