Dissolve based on common value

accessor · #1 (**permalink**) 02-10-04, 17:23

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

r123456 · #2 (**permalink**) 02-10-04, 22:51

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

Internet Services

Web Development

Digital Marketing

Consumer Tech