8

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!

0
10

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.

7

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.

0
5

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);
4

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.

-2

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.