CODEWITHSUNDEEP
mysql
Thrifty Coder
Thrifty Coder

Reputation: 41

SQL For each statement

I am working on some SQL homework and could someone explain to me how to get this question done.

  1. Display the average raw scores of team ‘Dolphins (1 point)

Here is an image of the data structure.

DataStructure

I need to make a query that returns the average raw score of 4 players. However, when I try executing the code below it just returns one average.

/* Question 2 */
SELECT AVG(RawScore)
FROM Bowler_Scores
WHERE BowlerID IN
(
SELECT BowlerID
FROM Bowlers
WHERE TeamID =
(
SELECT TeamID
FROM Teams
WHERE TeamName = "Dolphins"));

In bowler scores each bowler id can have multiple scores. For instance it may have the records - (43,101) (50,301) and (43,106).

I don't know how to write and sql statement that will get the average raw score for each player on that team out of all of there individual raw scores in the bowler scores table.

Upvotes: 0

Views: 1022

Answers (1)

Brad Cunningham
Brad Cunningham

Reputation: 30

If you need the average individual scores for each member of the Dolphins Team you can use this:

Select Teams.TeamName, Bowlers.BowlerID, avg(Rawscore)
from   Bowlers
inner join Teams
on    Bowlers.TeamId = Teams.TeamID
inner join  Bowler_Scores
on    Bowlers.BowlerID = Bowler_Scores.BowlerID
where  teams.teamname = 'Dolphins'
group by TeamName, BowlerID

If you just need one average score for the team then just remove the BowlerID from the SELECT and GROUP BY lines.

Upvotes: 1

Related Questions