Home Ask Login Register

Developers Planet

Your answer is one click away!

jb7890 February 2016

Summing like results in SQL

I need to list the total number of people who speak a language, so far I have come up with:

  (country.population * (language.percentage / 100)) 
ORDER BY name; 

And this returns every language and the number who speak it, but it does it for each country that has a population who speaks that language, for instance:

"Afghan Persian";1624568.00000000000000000000
"Afghan Persian";5269297.00000000000000000000
"Afghan Persian";1052017.50000000000000000000

What can I do to make only one row for each language with the sum of the total world population who speaks it?


Chris February 2016

You would need to use an aggregation function (i.e. sum) and a group-by. So your query would look something like:

select language.name , sum((country.population * (language.percentage / 100)))
from language, country
group by language.name
order by name

Gordon Linoff February 2016

First, you need a join. A simple rule: Never use commas in the FROM clause.

Then table aliases make the query easier to follow. And, you want an aggregation query . . . GROUP BY and SUM():

SELECT l.name, 
       SUM(c.population * (l.percentage / 100)) 
FROM language l JOIN
     country c
     ON l.countryId = c.countryId -- Or whatever the proper JOIN condition is
ORDER BY name;

Post Status

Asked in February 2016
Viewed 1,088 times
Voted 6
Answered 2 times


Leave an answer

Quote of the day: live life