CODEWITHSUNDEEP
sqldatabasefunctionpostgresql
Adam Smith
Adam Smith

Reputation: 11

SQL Function and order by result?

I have this relation; Country(name, code, capital, province, area, population)

from where I'd like to get the languages (name), the number of countries they are spoken in an the number of native speakers of each language.

I'm pretty sure I'm almost finished with this, but I don't think I've gotten the hang of what "ORDER BY" is really used for, because my calculation for count(l.name) gets messy and only gets me the values "1" when I know for certain that this database contains information where a certain language is spoken in several countries!

SELECT DISTINCT l.name, count(l.name) as SpokenIn, 
l.percentage*c.population as NativeSpeakers 
FROM (country c 
JOIN language l
ON c.code = l.country)
GROUP BY l.name, NativeSpeakers;

All other rows are fine, only the "SpokenIn" relation is messy. What am I doing wrong :(

I am using PostgreSQL.

Upvotes: 0

Views: 91

Answers (3)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

select l.name,
       counter.num_countries as SpokenIn,
       sum(l.percentage * c.population) as NativeSpeakers
  from country c
  join language l
    on c.code = l.country
  join (select name, count(country) as num_countries
          from language
         group by name) counter
    on l.name = counter.name
 group by l.name, counter.num_countries

Edit - fixed spelling error "counties" vs. countries

Upvotes: 1

Ash8087
Ash8087

Reputation: 701

Are you just trying to figure out how many people speak each language? If so, this might be a better way to structure the query:

    SELECT DISTINCT l.name, sum(l.percentage*c.population) as SpokenBy 
    FROM (country c 
    JOIN language l
    ON c.code = l.country)
    GROUP BY l.Name

Upvotes: 0

Pieter
Pieter

Reputation: 420

I'm no PostgreSQL expert but i think you made a litle grouping mistake. you are grouping over the NativeSpeakers column but you actually want the sum of all the nativespeakers. Try the following:

SELECT l.name, count(c.name) as SpokenIn, 
sum(l.percentage*c.population) as NativeSpeakers 
FROM (country c 
JOIN language l
ON c.code = l.country)
GROUP BY l.name;

if you want to fiddle some more with it: http://www.sqlfiddle.com/#!15/0d0bc/3

Upvotes: 0

Related Questions