Invalid object name after aliasing a table?

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.

You're trying to reference the alias in your from statement in a new subquery.

You could do something like that with a common table expression though.

;with a as (
select mID as movieID, avg(stars) as avgStars
  from Rating
  group by mID
)

select distinct A.movieID, A.avgStars 
from a
where  A.avgStars <= all(select A.avgStars from A);

The is failing because your Where clause. The all(select A.avgStars from A) cannot see the "A" table aliased above in this context. It thinks you're trying to link to a table named A.

The other answers have already identified the problem and how to solve it.

Here is another way, that simplifies the query further:

select top (1) with ties
    mID as movieID, avg(stars) as avgStars
from Rating 
group by mID
order by avgStars ;