Help finding the ratio

[soyunlocofl]

using MySQL version 3.23.58

Suppose i have two tables called tableA and tableB. TableA have 3 fields called area, employers, year and tableB also have 3 fields state, area and area_name.


tableA looks like this

area employers year
233 250 2005
654 500 2005
221 2060 2005
754 786 2005


tableB looks like this

area state area_name
233 FL anything
654 NY anything
221 FL anything
754 CA anything

How can i find the ratio of the people employed in florida versus new york?.

I have tried many things but nothing works, including this query that works but give me wrong a wrong answer.

SELECT sum(L.employers) / sum(L1.employers) FROM tableA L, tableB LL, tableA L1, tableB LLL WHERE L.area = LL.area AND LL.state = 'FL' and L1.area = LLL.area AND LLL.state = 'NY;

it looks like it worked but them a got wrong answer 2.31 when i should got 4.62 then i ran the same query but without dividing

SELECT sum(L.employers), sum(L1.employers) FROM tableA L, tableB LL, tableA L1, tableB LLL WHERE L.area = LL.area AND LL.state = 'FL' and L1.area = LLL.area AND LLL.state = 'NY';

and i got 2310 employers in FL and 1000 in NY. For some reason i'm getting double amount of employers in NY 1000 when it should only be 500. Is there anything i could do to fix this or should i find another way to do it. Thanks

 

[r937]

run two separate queries, you are getting cross join effects

r937.com | rudy.ca