Sqlalchemy How To Divide 2 Columns From Different Table

I have 2 tables named as company_info and company_income: company_info : | id | company_name | staff_num | year | |----|--------------|-----------|------| | 0 | A | 10

Solution 1:

Join the tables and do a standard sum. You'd want to either set yourself up a view in MySQL with this query or create straight in your program.

SELECT
  a.CompanyName,
  a.year,
  (a.staff_num / b.income) as avg_income

 FROM
  company_info as a

 LEFT JOIN
   company_income as b
 ON
   a.company_name = b.company_name
 AND
   a.year = b.year

You'd want a few wheres as well (such as where staff_num is not null or not equal to 0 and same as income. Also if you can have multiple values for the same company / year in both columns then you'll want to do a SUM of the values in the column, then group by companyname and year)

Solution 2:

Try this:

SELECT 
    info.company_name,
    (inc.income / info.staff_num) as avg,
    info.year
FROM
    company_info info JOIN company_income inc
ON
    info.company_name = inc.company_name
AND
    info.year = inc.year