CODEWITHSUNDEEP
mysqlsql
Faisal Malik
Faisal Malik

Reputation: 77

Group By in MySQL - Countries and their respective languages with most popular language on top

I have a table with a list of countries called countryand another table with a list of languages spoken in each country called countrylanguage. Both tables have a code for countries that a join can be used on.

I want to group by the countries and show the languages spoken in each country with the highest percentage language at top with the rest descending underneath it. Example:

    Aruba - Papiamento -  76.7 
            English    -  9.5
            Spanish    -  7.4
            Dutch      -  5.3

Same for each country in the table. Below is a sample of 10 rows of each table. I would greatly appreciate any help. Thank you! enter image description here

Upvotes: 1

Views: 1504

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

I recommend populating the country column on all rows:

select c.name, cl.language, cl.percentage 
from country c join
     countrylanguage cl
     on c.code = cl.countrycode
order by c.name, cl.percentage desc;

However, if you really want to skip the country on all but the first row, use row_number() and conditional logic:

select (case when row_number() over (partition by c.code order by cl.percentage desc) = 1
             then c.name
        end), cl.language, cl.percentage 
from country c join
     countrylanguage cl
     on c.code = cl.countrycode
order by c.name, cl.percentage desc;

Upvotes: 0

Ahmed
Ahmed

Reputation: 924

You can use LEFT JOIN to help you achieve what you ask for;

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.¹

Code Syntax

SELECT country.name, countrylanguage.language, countrylanguage.percentage FROM county
LEFT JOIN countrylanguage 
ON country.code = countrylanguage.countryCode
ORDER BY country.name ASC, countrylanguage.percentage DESC

OUTPUT EXAMPLE

|  name |  language  | percentage |
|:-----:|:----------:|:----------:|
| Aruba | Papiamento |    76.7    |
| Aruba |   English  |     9.5    |
| Aruba |   Spanish  |     7.4    |
| Aruba |    Dutch   |     5.3    |

LEFT JOIN Diagram

left join

So, why you can't use group by in your query?

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.²

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try the below -

select c.name,cl.language,cl.percentage 
from country c join countrylanguage cl on c.code=cl.countrycode
order by c.name, cl.percentage desc

Upvotes: 1

Related Questions