Reputation: 23
I just started learning MySQL and I got these tables from sqlzoo.net
game
id mdate stadium team1 team2
1001 8 June 2012 National Stadium, Warsaw POL GRE
1002 8 June 2012 Stadion Miejski (Wroclaw) RUS CZE
1003 12 June 2012 Stadion Miejski (Wroclaw) GRE CZE
1004 12 June 2012 National Stadium, Warsaw POL RUS
...
goal
matchid teamid player gtime
1001 POL Robert Lewandowski 17
1001 GRE Dimitris Salpingidis 51
1002 RUS Alan Dzagoev 15
1001 RUS Roman Pavlyuchenko 82
...
I am using the following code to show the match id where the goal time is more than 20
SELECT goal.matchid
FROM game, goal
WHERE game.id = goal.matchid AND goal.gtime > 20
That worked well, although, now I want to show the match id where the goal time average is more than 20 but I'm at a loss.
1 more question!
For example I have 1 more table: coach(id, teamid, coachname) I want to find all goals between 25-50 (gtime) by showing all columns in goal and coachname column in coach
SELECT matchid, goal.teamid, player, gtime, coachname
FROM goal JOIN coach ON (goal.teamid=coach.teamid)
WHERE gtime BETWEEN 25 AND 50
That worked well but I'm wondering if there is any simpler code so I don't have to write all the columns from goal table. Thanks!
Upvotes: 2
Views: 61
Reputation: 133410
Could be you are looking for somethig like this
SELECT goal.matchid
FROM game, goal
WHERE game.id = goal.matchid
having avg(goal.gtime) > 20
group by goal.matchid
Upvotes: 3
Reputation: 17953
You really don't even need the game
table and the join for this result.
select matchid
from goal
having avg(gtime) > 20
group by matchid
Another of the answers retained the where
clause filter on gtime > 20
. It you do that then all the matches with goals after the 20th minute will have averages that qualify on that limited data set. In other words the average of all values greater than 20 is going to exceed 20 also. The avg()
aggregate only applies to rows that make it past the where
conditions.
Upvotes: 1
Reputation: 193
You can use AVG() function to find Average.
SELECT goal.matchid
FROM game, goal
WHERE AVG(goal.gtime) > 20
I couldn't recall simpler code for second one.
Upvotes: -1