题目:sql题目
通过建立子查询
SELECT Director,sum(Domestic_sales+International_sales) as total_sale,
count(id) as num_movie ,sum(Domestic_sales+International_sales)/count(id) as avg_sale
FROM movies
join Boxoffice
on id = movie_id
group by Director
having num_movie >1 and avg_sale =(
select max(avg) from(
select sum(Domestic_sales+International_sales)/count(id) as avg
FROM movies
join Boxoffice
on id = movie_id
group by Director
having count(id) >1
)
);