Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Dissolve based on common value

    I am looking for help with a SQL statement to do the following:
    Compare each value in [FIELD2] with every other value in [FIELD2]. Then, for all exact matches, perform % calculations on corresponding values in [FIELD3] and [FIELD4] to update [FIELD5]. ex:

    [FIELD1] - [FIELD2] - [FIELD3] - [FIELD4] - [FIELD5]
    North - China - 20 - 40 - 50.0%
    Central - Japan - 30 - 90 - 33.3%
    South - China - 10 - 10 - 100.0%
    East - Japan - 50 - 200 - 25.0%

    The desired output would be like:
    Overall China has [100*(20+10)/(40+10)]=60.0% apples
    and
    Overall Japan has [100*(30+50)/(90+200)]=27.5% apples

    ...maybe by creating an array for all values in [FIELD2] using PHP for the comparison??

    Regards
    Accessor

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select t1.f1, t1.f2, t1.f3, t1.f4, 100 * ((t1.f3 + t2.f3)/(t1.f4+t2.f4)) As "%"
    from table t1
    INNER JOIN
    table t2 ON
    t1.f2 = t2.f2 AND
    NOT t1.f1 = t2.f1;

    Returns,
    Code:
    F1         F2                 F3         F4          %
    ---------- ---------- ---------- ---------- ----------
    South      China              10         10         60
    North      China              20         40         60
    East        Japan              50        200        27.5862069
    Central   Japan              30         90         27.5862069
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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