I’m a newbie in databases and SQL worl. I have a problem. I’ve spent 12 hours but couldn’t find the solution.
Can anyone give me a sample code, or show me the right direction please?
Here is the scenario:
I will need to find out the number of goals a player has scored in year 2009, and display various information about him such as name, shirt no etc.
The only part I couldn’t figure out is the bit where I have to add all the goals scored by a certain player, and display his name, position, total number of goals scored in 2009, and shirt no.
I will be grateful if anyone can help me on this, otherwise I’m going to go mad.
Sorry for not including the code in the first place.
This is what i’ve done so far:
SELECT players.name, teams.teamName, performance.shirtNo, players.position, performance.goals FROM players, performance, teams WHERE goals = (SELECT MAX(goals) FROM performance) AND players.teamID = teams.teamID AND players.teamID = performance.teamID;
E.g:Which results in:
name - teamName - shirtNo - position - goals
Henry - Barcelona - 9 - FW - 9
This information is based on the maximum goal in the performance table. But I need to add all the goals scored by each player individualy and display the above information based on the highest goal (E.g. total goals scored by the player with the teamID=2).
I’ve used “COUNT(),” “SUM() GROUP BY teamID” statement, I can count the number of goals scored by each player, but I can’t combine the two code. (E.g Count the goals for each player, display the above infromation based on the highest goal) I’ve looked into JOIN, subqueries and I just couldn’t figure it out.
Lets say we’ve got a “playedMatches” table. This table stores each player’s performance based on number of goals scored, minutes played etc with a unique teamID and playedMatchesID. If a player has played in 3 matches, the table will store player’s performance for each of those 3 matches. What I want to do is, add the goals for each player, and display information from 3 different tables based on that player (or who ever has scored the highest goals).
Is this even possible in mysql?
To be more precise, I want to sum all the goals in the performance table, then group by to see who has scored the highest goals, and display that players name shirt number, position etc. ( which is held in 3 different tables )
In the above example, I can display a players information who has scored the highest goals in a single game, but i need to display the player who has scored the most goals in all games.
Does that make sense? If not I’ll try explaining it again
But I need to add the goals before finding the MAX goals, and thats what I can’t seem to figure out. I need to add the goals for each player, then find the highest. I’ve uploaded a screenshot, the player with the teamID = 2 has played two games, in the first game he has scored 4 goals, in the second game he has scored 5 goals, now I need to add these two games (for each player) then find the highest.
select name,shirtno,position,SUM(goals) from players p LEFT JOIN playerPerformance pp on pp.shirtNo = p.shirtNo LEFT JOIN playedMatches pm on pm.matchID=pp.matchID group by shirtNo
SELECT
p.name
,p.shirtNo
,p.position
,SUM(IF(YEAR(pm.date) = 2009,pp.goals,0)) goals_09
,SUM(pp.goals) lifetime_goals
FROM
players p
LEFT OUTER
JOIN
playerPerformance pp
ON
p.shirtNo = pp.shirtNo
LEFT OUTER
JOIN
playerMatches pm
ON
pp.matchID = pm.matchID
GROUP
BY
p.shirtNo
no it’s next week, I’ve actually done it, but it was pure guess work and help from you guys, and trying out different statements for the last 7 days.
I’m trying to understand the code now, but I still don’t get LEFT JOIN, subqueries, nested queries etc. So I’m looking those up, but the assignment is done never the less, will hand it in next week, thank you guys
Can you suggest me some tutorials or books on this stuff? I want to be comfortable with SQL, also there’s an exam soon
