I have no idea how to group by by the column from subquery.

I want to group by languageas below:

enter image description here

Here is my code:

select a.name, count(a.language) as count
from
    (select 
        temp2.name,
        countrylanguage.language
    from 
        countrylanguage
    right join  
        temp2
    on 
        temp2.code = countrylanguage.countrycode
    ) as a
group by a.language;

Edited

I got a solution as below:

select temp2.name, count(countrylanguage.language) 
from countrylanguage 
join temp2 on temp2.code = countrylanguage.countrycode 
group by temp2.name;

2 Answers

2
fa06 On Best Solutions

You can try below - you don't need any subquery

select temp2.name,count(countrylanguage.language)
  from countrylanguage join temp2
    on temp2.code = countrylanguage.countrycode
group by temp2.name
2
meysam poormonfared azimi On

Table data : SELECT * FROM countrylanguage

enter image description here


SELECT * FROM temp2

enter image description here

SELECT a.[Name], SUM(CASE WHEN a.[language] IS NULL THEN 0 ELSE 1 END) language_count FROM (SELECT temp2.[Name], countrylanguage.[language] FROM temp2 LEFT JOIN countrylanguage ON temp2.code = countrylanguage.countrycode) a

GROUP BY a.name

enter image description here