SQL Server : error in a select statement

Question

I created a database that contains info about movies and actors etc. Among others, I've created these tables:

CREATE TABLE Movie
(
   Movie_ID PRIMARY KEY NOT NULL idenity(1,1),
   Movie_title varchar(50),
   ....
)

CREATE TABLE Actor
(
   Actor_ID PRIMARY KEY NOT NULL identity(1,1),
   Actor_Name varchar(50),
   Actor_Surname varchar(50),
   .....    
)

and I link them using the following table:

CREATE TABLE Has_Actor
(
    Has_Actor_ID PRIMARY KEY NOT NULL identity(1,1),
    Has_Actor_M_ID foreign key references Movie(Movie_ID),
    Has_Actor_A_ID foreign key references Actor(Actor_ID),
)

Now I want to view the actor's who played in the most movies, name and surname, so I tried the following select statement:

SELECT 
    Actor.Actor_Name, Actor.Actor_Surname
FROM 
    Actor, Has_Actor
WHERE 
    Has_Actor_A_ID = Actor.Actor_ID 
    AND Actor.Actor_ID = (SELECT MAX(distinct COUNT(Has_Actor.Actor_ID)))

And when I execute this I get this error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I know that there is a mistake at the point where i use the max and count functions but I can't understand what is wrong. can you please help me?

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was discontinued with the ANSI-92 SQL Standard (more than 20 years ago!) — marc_s
– marc_s, Commented Mar 27, 2014 at 11:59
in this sub query you have not mentioned table name SELECT MAX(distinct COUNT(Has_Actor.Actor_ID)) — Mohini Mhetre
– Mohini Mhetre, Commented Mar 27, 2014 at 12:02

Gordon Linoff · Accepted Answer · 2014-03-27 12:07:02Z

2

MAX(distinct COUNT) . . . that is a strange construct. Aggregation functions don't nest.

You are clearly learning SQL, so I've fixed your query, so you can learn to do the following:

ALWAYS use explicit join syntax. NEVER use a comma in the from clause.
Use table aliases (abbreviations) to make the query more readable.
Use them for all column references.

This is the query that you want. It aggregates by actor, orders by the number of movies, and choses the top value:

SELECT TOP 1 a.Actor_Name, a.Actor_Surname
FROM Actor a join
     Has_Actor ha
     on ha.Has_Actor_A_ID = a.Actor_ID
GROUP BY a.Actor_Name, a.Actor_Surname
ORDER BY COUNT(*) desc

EDIT:

If you want all actors (which was ambiguous in the question), then you can use the WITH TIES option to TOP (as suggested in Hamlet's comment). Otherwise, you express the query using a CTE:

WITH ActorCounts as (
      SELECT a.Actor_Name, a.Actor_Surname, COUNT(*) as NumMovies
      FROM Actor a join
           Has_Actor ha
           on ha.Has_Actor_A_ID = a.Actor_ID
      GROUP BY a.Actor_Name, a.Actor_Surname
     )
SELECT Actor_Name, Actor_Surname
FROM ActorCounts ac
WHERE NumMovies = (SELECT MAX(NumMovies) FROM ActorCounts);

edited Mar 27, 2014 at 12:07

answered Mar 27, 2014 at 12:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hamlet Hakobyan Over a year ago

TOP 1 WITH TIES is rather.

Madhivanan · Accepted Answer · 2014-03-27 12:48:24Z

0

Try this

SELECT Actor.Actor_Name, Actor.Actor_Surname
FROM Actor, 
    (
    SELECT TOP 1 Actor_ID,COUNT(Actor_ID) as counting from Has_Actor
    Group by Actor_ID order by COUNT(Actor_ID) DESC
    )
 Has_Actor
WHERE Has_Actor_A_ID=Actor.Actor_ID

edited Mar 27, 2014 at 12:48

answered Mar 27, 2014 at 11:59

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

1 Comment

Vivian Over a year ago

tried this, got the following errors: in select top 1 and in group by statements: the multipart identifier 'Actor_ID' could not be bound. in both count functions: Invalid Column name 'Actor_ID' in where statement: invalid column name Has_Actor_A_ID

Collectives™ on Stack Overflow

SQL Server : error in a select statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related