CODEWITHSUNDEEP
sqlsql-server-2008
Colleen
Colleen

Reputation: 25489

SQL Server 'invalid column name' error

I have (from the imdb database) a casts table and a movie table. I'm trying to find the name and cast size of the movie with the largest cast (distinct actors).

This is what I came up with, but I'm getting an

"invalid column name" error on "totalcount"

Why?

Also, does this query look right?

select name, count(distinct pid) as totalcount 
from casts join movie on mid=movie.id 
where totalcount =
    (select max(CastCount.total) 
    from (select count(distinct pid) as total from casts group by mid)CastCount) 
group by name;

Upvotes: 5

Views: 8811

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53840

Something like this?

SELECT movie.name, COUNT(casts.id) AS castsize
FROM cast
JOIN movie
ON movie.id = casts.mid
GROUP BY casts.mid
HAVING COUNT(casts.id) = MAX(COUNT(casts.id))

Upvotes: 0

RobO
RobO

Reputation: 41

In MySQL you can't reference an aliased column by its alias in a WHERE clause.

Your best bet is not to select where the count equals a selected max. Instead, order by the count from high to low, and limit to the first row.

Upvotes: 1

a1ex07
a1ex07

Reputation: 37364

You cannot use aliases in WHERE. Since it's aggregate, you should replace it with HAVING: HAVING totalcount = ... Also, your subquery may return more than 1 row, and you will have another error.

Upvotes: 2

Related Questions