CODEWITHSUNDEEP
phpmysqlsqldatabase
user2949176
user2949176

Reputation: 11

Find the top scorer in a league

I am new to databases. I have been given an assignment which is something to do with football league where some teams play home and away and the scores are stored etc etc. I have the following tables 

goals
======================
goal_id 
goal_player_people_id
goal_game_id
goal_score

player
=====================
player_people_id
player_team_id

people
====================
people_id
people_first_name
people_last_name
people_dob

I need to find out the name of the top scorer, please help.

Upvotes: 0

Views: 186

Answers (2)

Nik Terentyev
Nik Terentyev

Reputation: 2310

if goals.goal_player_people_id is a reference to people.people_id:

SELECT p.people_first_name, p.people_last_name, SUM(g.goal_score) totscore
FROM goals g
JOIN people p
ON g.goal_player_people_id = p.people_id
GROUP BY p.people_id
ORDER BY totscore DESC LIMIT 1;

Upvotes: 1

rshetye
rshetye

Reputation: 667

select  people_first_name people_last_name
from people
where people_id not in(
    select A.id
    from(
        select goal_player_people_id as id,count(*) as gc
        form goals
        group by goal_player_people_id) A,
        (
        select goal_player_people_id as id,count(*) as gc
        form goals
        group by goal_player_people_id) B,
    where A.gc<B.gc
)

Upvotes: 0

Related Questions