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:

SELECT 
  language.name, 
  (country.population * (language.percentage / 100)) 
FROM 
 language, 
 country
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?

Answers


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
GROUP BY name
ORDER BY name;

Post Status

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

Search




Leave an answer


Quote of the day: live life