CODEWITHSUNDEEP
mysqlsql
Ron
Ron

Reputation: 11

Derived joins and aggregate functions throwing errors

I am stuck in a query. i have two tables player and player_attributes with player_api_id as primary key.

I need to find the youngest,oldest player and the average overall rating of the oldest and youngest player.

Query to write the youngest and oldest player:

select player_name, birthday,YEAR(CURDATE()) - YEAR(birthday) as age from player where 
birthday=(select max(birthday) from player)
or
birthday=(select min(birthday) from player)

Query for the average overall rating of all players:

SELECT player_api_id, avg(overall_rating) as avg_score,
FROM (
  SELECT player_api_id, overall_rating FROM player_attributes 
) as p
GROUP BY player_api_id;

Error while joining:

select player_api_id, avg(overall_rating),min(birthday),max(birthday) as avg_score
FROM (
  SELECT player_api_id, overall_rating FROM player_attributes
) as p
join
(select birthday from player) as p1
on p.player_api_id=p1.player_api_id
GROUP BY player_api_id;

I am confused now??

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

There is no reason to use subqueries just to select columns. In fact, in MySQL, it is a really, really bad idea -- because MySQL materializes the subqueries.

So, just do:

select pa.player_api_id, avg(overall_rating) as avg_score,
       min(p.birthday), max(p.birthday)
from player_attributes pa join
     player p 
     on pa.player_api_id = p.player_api_id
group by pa.player_api_id;

I'm not sure if the rest of the logic is okay. But this should at least fix the syntax error.

Upvotes: 1

Related Questions