CODEWITHSUNDEEP
sqljoinunion
Rohit Phansalkar
Rohit Phansalkar

Reputation: 1

Use count and join in sql

There are two tables having country name,top football clubs from those countries. I want to join the table and count the clubs from each country. following code doesn't work:

-- Table names - table2017, table2018

select t.Country, COUNT(t.Country) as '2017 Countries',
       COUNT(a.Country) as '2018 countries'
from table2017 as t
Join table2018 as a
on a.Country = t.Country
group by t.Country

o/p 

Country 2017 Countries  2018 countries
England       64             64
France        1               1
Germany       9               9
Italy        25               25
Spain         9               9

expected answer :

Country 2017 Countries  2018 countries
England       8              8
France        1              1
Germany       3              3
Italy         5              5
Spain         3              3

What am i doing wrong?

Upvotes: 0

Views: 70

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

Try joining the subquery for each count 

select 
    t1.country, t1Countries_2017, t2.Countries_2018
from 
    (select 
         t.Country, count(t.Country) Countries_2017
     from 
         table2017 
     group by 
         t.Country) t1 
inner join  
    (select 
         t.Country, count(t.Country) Countries_2018
     from 
         table2018
     group by 
         t.Country) t2 on t1.country = t2.country

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You need to aggregate before joining:

select t2017.Country, t2017.num_2017, t2018.num_2018
from (select t.country, count(*) as num_2017
      from table2017 t
      group by t.country
     ) t2017 join
     (select t.country, count(*) as num_2018
      from table2018 t
      group by t.country
     ) t2018
     on t2017.Country = t2018.Country;

COUNT() simply counts the number of non-NULL values. This two counts are the same after the JOIN, so the code doesn't do what you want.

Upvotes: 1

Related Questions