sqlalchemy how to divide 2 columns from different table

Question

I have 2 tables named as company_info and company_income:

company_info :

| id | company_name | staff_num | year |
|----|--------------|-----------|------|
| 0  | A            | 10        | 2010 |
| 1  | A            | 10        | 2011 |
| 2  | A            | 20        | 2012 |
| 3  | B            | 20        | 2010 |
| 4  | B            | 5         | 2011 |

company_income :

| id | company_name | income | year |
|----|--------------|--------|------|
| 0  | A            | 10     | 2010 |
| 1  | A            | 20     | 2011 |
| 2  | A            | 30     | 2012 |
| 3  | B            | 20     | 2010 |
| 4  | B            | 15     | 2011 |

Now I want to calculate average staff income of each company, the result looks like this:

result :

| id | company_name | avg_income | year |
|----|--------------|------------|------|
| 0  | A            | 1          | 2010 |
| 1  | A            | 2          | 2011 |
| 2  | A            | 1.5        | 2012 |
| 3  | B            | 1          | 2010 |
| 4  | B            | 3          | 2011 |

how to get this result using python SQLalchemy ? The database of the table is MySQL.

score 1 · Answer 1 · answered Apr 23 '18 at 05:46

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)

answered Apr 23 '18 at 05:46

Rando

473
2
12

2

Python.. sqlalchemy..? – thebjorn Apr 23 '18 at 05:52
@thebjorn The user stated: The database of the table is MySQL. This can be created with a view to be collected via sqlalchemy. They can then link this view using something like: https://stackoverflow.com/questions/9766940/how-to-create-an-sql-view-with-sqlalchemy?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa or http://docs.sqlalchemy.org/en/latest/core/reflection.html – Rando Apr 23 '18 at 05:58

score 0 · Accepted Answer · answered Apr 23 '18 at 05:48

0

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

answered Apr 23 '18 at 05:48

Rahul Jain

1,319
7
16

3

Python.. sqlalchemy..? – thebjorn Apr 23 '18 at 05:52

sqlalchemy how to divide 2 columns from different table

2 Answers2

Linked