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 &lt;= 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 &lt;= ALL (SELECT AvgStars FROM Avgs);


You shouldn't need DISTINCT there, either.