230628_IL(I_Learned)_SQL

어디에나 있는 모든 하위 쿼리(SELECT, FROM 절, 어디든)

두 개의 SELECT 하위 쿼리에서 평균 홈 및 원정 팀 목표 수를 추출합니다.
기본 쿼리의 특정 단계에 대한 평균 홈 및 원정 골을 계산합니다.
2012/2013 시즌의 데이터만 포함되도록 하위 쿼리와 기본 쿼리를 모두 필터링합니다.
stage 열을 기준으로 쿼리를 그룹화합니다.

SELECT
	m.stage,
	ROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,
	ROUND((SELECT AVG(home_goal + away_goal)
		FROM match
		WHERE season = '2012/2013'),2) AS overall
FROM match AS m
WHERE m.season = '2012/2013'
GROUP BY m.stage;

FROM에 하위 쿼리 추가

SELECT
	s.stage,
	ROUND(s.avg_goals,2) AS avg_goals
FROM
	(SELECT
		stage,
		AVG(home_goal + away_goal) AS avg_goals
	FROM match
	WHERE season = '2012/2013'
	GROUP BY stage) AS s
WHERE
s.avg_goals > (SELECT AVG(home_goal + away_goal)
				FROM match WHERE season = '2012/2013');

SELECT에 하위 쿼리 추가

SELECT
	s.stage,
	ROUND(s.avg_goals,2) AS avg_goal,
	(SELECT AVG(home_goal + away_goal) FROM match WHERE season = '2012/2013') AS overall_avg
FROM
from match
	(SELECT
		stage,
		AVG(home_goal + away_goal) AS avg_goals
	FROM match
	WHERE season = '2012/2013'
	GROUP BY stage) AS s
WHERE
	s.avg_goals > (SELECT AVG(home_goal + away_goal)
					FROM match WHERE season = '2012/2013');