CODEWITHSUNDEEP
mysqldatabase
Dale12
Dale12

Reputation: 23

Finding the average using mysql

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

Answers (3)

ScaisEdge
ScaisEdge

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

shawnt00
shawnt00

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

Chathuranga Shan
Chathuranga Shan

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

Related Questions