This query gives a list of languages assocated with a number of agents who speak

Question

This query gives a list of languages assocated with a number of agents who speak it.

I want to add to this statement so I can get the languages with the highest number of speakers.

Please only use group by, having, from, and nested statements.

You may use min, max, all, and avg if need be

select l.language, (select count(lr.agent_id) from languagerel lr where lr.lang_id = l.lang_id) as num_speakers from language l;

This what the above query gives:

language | num_speakers
------------+--------------
English    |           12
Spanish    |           92
German     |          122
French     |          101
Chinese    |           82
Japanese   |          112
Russian    |          103
Polish     |          105
Arabic     |          118
Hebrew     |          101
Cherokee   |          105
Korean     |          112
Vietnamese |          111
Pashtu     |          111
Farsi      |          104
Bengali    |           89
Malay      |          102
Portuguese |          108
Hindi      |          110
Turkish    |           91
(20 rows)

Explanation / Answer

SELECT l.language, (SELECT count(lr.agent_id) FROM languagerel lr WHERE lr.lang_id = l.lang_id) AS num_speakers FROM language l ORDER BY num_speakers DESC;

The above will print the highest agents having languages first...

SELECT l.language, (SELECT count(lr.agent_id) FROM languagerel lr WHERE lr.lang_id = l.lang_id) AS num_speakers FROM language l ORDER BY num_speakers DESC LIMIT 3;

this will print top 3 languages with highest number of agents..

SELECT l.language, (SELECT count(lr.agent_id) FROM languagerel lr WHERE lr.lang_id = l.lang_id) AS num_speakers FROM language l HAVING MAX( num_speakers ) = num_speakers;

this prints only the highest agents having countries....

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.