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.

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 ;

According to the condition you were trying to apply:

A.avgStars <= all(select A.avgStars from A)

your query is supposed to return only the entries with the lowest average. The same logic can be expressed in a simpler way with top (1) with ties + order by, as shown above.

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.

Try executing with AS

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