Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Sum a column and group it, then divide by a field in another table

I have two tables, one that looks like this:

Lis of companies and revenue

The other has the countries’ populations:

enter image description here

I would like to sum up the revenue by country (I know how to do this part, e.g Select Sum(Revenue), Country from COMPANIES GROUP BY Country;)

and then divide it by the population from the other table to get per capital revenue , so the final result should be

enter image description here

Tags:

2

Answers


  1. 0

    That will be enough to get what you need (make sure that the Revenue column is not only an integer).

    SELECT sumRev/p.Population, q.Country
FROM (SELECT SUM(Revenue) sumRev, Country FROM COMPANIES GROUP BY Country) q
JOIN Population p ON q.Country = p.Country
    Login or Signup to reply.
  2. 0

    If you have this set: sql_mode=only_full_group_by

    Then it will need to be like this:

    SELECT companies.country, 
       round(sum(revenue) / country.population, 2) as RevPerCap  
FROM companies
join country on country.name = companies.country  
group by companies.country, country.population
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search