Invalid object name after aliasing a table?

When I run the following query

select distinct A.movieID, A.avgStars from 
(select mID as movieID, avg(stars) as avgStars
from Rating
group by mID) A
where  A.avgStars <= all(select A.avgStars from A);

I get the error "Invalid object name 'A'."

Do you know why I'm getting this?

Thanks!

You can't reference an alias from a subquery at the same scope. You can do this with a CTE, though; perhaps you meant:

;WITH Avgs AS
(
  select mID as movieID, avg(stars) as avgStars
  from Rating group by mID
)
SELECT movieID, avgStars FROM Avgs
WHERE avgStars <= ALL (SELECT AvgStars FROM Avgs);

You shouldn't need DISTINCT there, either.

StackExchange Database Administrators Q#163941, answer score: 11