Nested Queries/ JOIN IN SQL-CodePudding

Question: List the name of the team and number of wins of all teams that had more wins than the average team had. Order results by descending number of wins.

SELECT mlb_team.name, mlb_team.wins, AVG(mlb_team.wins)
FROM mlb_team
GROUP BY mlb_team.name
HAVING mlb_team.wins > AVG(mlb_team.wins);

My current solution is giving me the error Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'comp3421.mlb_team.wins' which is not functionally dependent o.

CodePudding user response：

SELECT mlb_team.name, SUM(mlb_team.wins)
FROM mlb_team
GROUP BY mlb_team.name
HAVING SUM(mlb_team.wins) > AVG(mlb_team.wins)
ORDER BY SUM(mlb_team.wins) DESC;

Note: I am not familiar with mysql but largely this is how it should be.

CodePudding user response：

GROUP BY requires every column that is not aggregated.

In your code, the GROUP BY expects mlb_team.name and mlb_team.wins, not just mlb_team.name.

Here is the code:

SELECT mlb_team.name, mlb_team.wins, AVG(mlb_team.wins)
FROM mlb_team
GROUP BY mlb_team.name,mlb_team.wins
HAVING mlb_team.wins > AVG(mlb_team.wins);