using MAX() and COUNT() with HAVING

Question

i have 2 tables movie:

insert into movie values ('mov1','2014-01-22','actor11');
insert into movie values ('mov2','2015-01-25','actor12');
insert into movie values ('mov1','2016-02-22','actor12');
insert into movie values ('mov1','2017-04-20','actor12');

dir:

insert into dir values ('d1','mov1','us',3);
insert into dir values ('d1','mov1','ind',3);
insert into dir values ('d2','mov2','uk',4);
insert into dir values ('d2','mov3','ind',3);

i want to find name of actor who has worked with most number of directors:

code:

SELECT actor, COUNT(actor) as c 
FROM   movie a  
       inner join dir b 
       on a.moviename=b.moviename  
GROUP BY actor
HAVING COUNT(actor)=(
                       SELECT MAX(mycount) 
                       FROM   ( 
                                  SELECT actor, COUNT(actor) as mycount 
                                  FROM   movie a 
                                         inner join dir b 
                                          on a.moviename=b.moviename 
                                  GROUP BY actor
                               )
                     );

i think the code is correct but im getting the error: Incorrect syntax near ')'.

Pls help!!

i just help you to re-format your query. You missed out the alias for the last subquery. Also your query can be much simplified — Squirrel, Feb 21 '20 at 06:01
ohh yess thanks! and why does last subquery requires an alias — Adi, Feb 21 '20 at 06:04
I would call it `derived table`. The SQL Server engine expects a name for `derived table`. The doc https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15 does stated that `When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the **required** table_alias .....` — Squirrel, Feb 21 '20 at 06:37

score 0 · Answer 1 · answered Feb 21 '20 at 06:02

Add Alias Name:

SELECT actor, COUNT(actor) as c 
FROM movie a inner join dir b 
on a.moviename=b.moviename  GROUP BY actor
HAVING COUNT(actor)=(
SELECT MAX(mycount) 
FROM ( 
SELECT actor, COUNT(actor) as mycount 
FROM movie a inner join dir b 
on a.moviename=b.moviename 
GROUP BY actor)t);

score 0 · Accepted Answer · answered Feb 21 '20 at 06:05

0

Try something like this

SELECT TOP (1) actor, COUNT(d.col1) as c 
FROM   movie a  
inner join dir b 
on a.moviename=b.moviename 
Group BY actor
ORDER BY c DESC

answered Feb 21 '20 at 06:05

Jaydip Jadhav

12,179
6
24
40

Nguyễn Văn Phong · Answer 3 · 2020-02-21T06:16:06.577

0

Demo on db<>fiddle

You need to add the alias name as @Squirrel's comment.

Refer to this post to know the reason why.

subquery in FROM must have an alias

However, your query is quite complex unnecessarily.

Refactor code

SELECT TOP 1 actor, COUNT(actor) as mycount 
  FROM movie a2 inner join dir b2 
  on a2.moviename=b2.moviename 
  GROUP BY actor
  ORDER BY COUNT(actor) DESC

edited Feb 21 '20 at 06:16

answered Feb 21 '20 at 06:06

Nguyễn Văn Phong

13,506
17
39
56

@Adi Does this answer your question? Please give me your feedback, Thanks. – Nguyễn Văn Phong Feb 22 '20 at 02:43

using MAX() and COUNT() with HAVING

3 Answers3

Linked