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
Accessorselect 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,
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
Showing posts with label compare. Show all posts
Showing posts with label compare. Show all posts
Subscribe to:
Posts (Atom)