CODEWITHSUNDEEP
sqlsql-serverdatabase
Vivian
Vivian

Reputation: 43

SQL Server : error in a select statement

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?

Upvotes: 0

Views: 217

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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:

  1. ALWAYS use explicit join syntax. NEVER use a comma in the from clause.
  2. Use table aliases (abbreviations) to make the query more readable.
  3. 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);

Upvotes: 2

Related Questions