SQL: Find country name of the team having the most players who have never scored a goal

Question

I have the following tables:

create table Players (
id integer,
name varchar(50) not null,
birthday date,
memberOf integer not null,
position varchar(20).
primary key (id),
foreign key (memberOf) references Teams(id)
);

create table Goals (
id integer,
scoredIn integer not null,
scoredBy integer not null,
timeScored integer not null,
rating varchar(20),
primary key (id),
foreign key (scoredIn) references Matches(id),
foreign key (scoredBy) references Players(id)
);

create table Teams (
id integer,
country varchar(50) not null,
primary key (id)
);

I have the following data in the above tables:

PLAYERS:

id       |    name     |     birthday     |     memberof    |     position
7           Mina            1997-01-20         1                 Captain
9           John            1997-09-01         1                 Quarterback 
2           Minnie          1995-10-13         3                 Goalkeeper
13          Lisa            1997-03-27         4                 Captain 
12          Rina            1995-01-03         2                 Fullback
11          Jasper          2002-09-22         1                 Halfback
17          Rose            1997-02-11         1                 Goalkeeper
22          Parvin          1993-03-09         3                 Goalkeeper
25          Nasom           1996-12-29         3                 Fullback

GOALS:

id    |     scoredin    |    scoredby    |    timescored     |     rating
1           10               7                60                   amazing
2           10               7                30                   okay
3           10               7                90                   amazing
4           20               9                119                  nice
5           20               9                80                   amazing
6           20               9                75                   amazing
7           30               2                30                   nice 
8           30               2                90                   amazing
9           40               13               110                  amazing

TEAMS:

id    |    country
1          Australia
2          Malaysia
3          Japan
4          Thailand

I am trying to output the country name of the team which has the most players who have never scored a goal. The output should be:

Country     |     Players 
Australia         2
Japan             2

I have the following view, which gives the count of players who have never scored a goal for each country:

create or replace view zerogoals as
select t.country, count(*)
from (
select distinct p.id, p.name, p.memberof, g.scoredby
from players p
full outer join goals g
on p.id = g.scoredby where scoredby is null
) s
inner join teams t on t.id = s.memberof group by t.country;

The above query gives me the following output:

country    |     count
Australia        2
Japan            2
Malaysia         1

I tried using the max function to get the desired output:

select country, max(count)
from zerogoals
group by country;

However I get the following output:

country    |    max
Australia       2
Japan           2
Malaysia        1

I am not sure how to get the tuples in the view zerogoals with the maximum value for the attribute count. Any insights are appreciated.

Answer 1

SELECT DISTINCT(player)
  FROM game JOIN goal ON matchid = id 
    WHERE (team1='GER' AND team2!='GER' OR team1!='GER' AND team2='GER') AND teamid!= 'GER'

It not that pretty but it works.

Answer 2

To get the number of players per country with no goal, you can use:

select t.name, count(*) as num_players_no_goal
from team.t join
     player p
     on t.id = p.memberof
where not exists (select 1
                  from goals g
                  where g.scoredby = p.id
                 )
group by t.name;

To limit this to just the maximum number, use window functions:

select name, num_players_no_goal
from (select t.name, count(*) as num_players_no_goal,
             rank() over (order by count(*) desc) as seqnum
      from team.t join
           player p
           on t.id = p.memberof
      where not exists (select 1
                        from goals g
                        where g.scoredby = p.id
                       )
      group by t.name
     ) t
where seqnum = 1;

One slight caveat is that this returns no teams if all players on all teams have scored goals. It is easily modified for that situation, but I'm guessing that you would rather return zero teams than all teams if that were the case.

Answer 3

You can use a CTE:

with cte as (
  select
    t.id, t.country, count(*) players 
  from teams t inner join (
    select * from players 
    where id not in (select scoredby from goals)
  ) p on p.memberOf = t.id
  group by t.id, t.country
)
select country, players 
from cte
where players = (select max(players) from cte)
order by country

See the demo.
Results:

country   | players 
Australia |       2
Japan     |       2

Answer 4

You could try using a inner join between the player, team and the list of not in goals ordered by count and limit to 1

select  t.name , count(*)
from player p
INNER JOIN  team t ON t.id = p.memberof
inner join (
select  p.id 
from  PLAYERS p 
where p.id  NOT IN  (
  select scoredby 
  from GOALS
) ) t1 on t1.id  = p.id 
group by t.name 
order by count(*) desc 
limit 1 

if you want all the max then

select  t.name , count(*)
from player p
INNER JOIN  team t ON t.id = p.memberof
inner join (
select  p.id 
from  PLAYERS p 
where p.id  NOT IN  (
  select scoredby 
  from GOALS
) t1 on t1.id  = p.id 
group by t.name 
having  count(*) = (
select  t.name , count(*)
from player p
INNER JOIN  team t ON t.id = p.memberof
inner join (
select  p.id 
from  PLAYERS p 
where p.id  NOT IN  (
  select scoredby 
  from GOALS
) t1 on t1.id  = p.id 
group by t.name 
 order by count(*) 
 limit 1 
)