CODEWITHSUNDEEP
sqlgroup-by
user
user

Reputation: 253

Can't solve a problem using group by function

The task: Obtain a list, in alphabetical order, of actors who've had at least 30 starring roles.

My code:

select name, count(ord=1)
from casting
join actor on actorid=actor.id
where ord=1 and count(ord=1) and exists ( select 1 from casting
 where count(movieid)>=30)
group by actorid,name
order by name

It gives me error, - invalid use of group by function.

Upvotes: 0

Views: 434

Answers (5)

Didarul Islam
Didarul Islam

Reputation: 1

SELECT actor.name FROM actor 
JOIN casting ON actor.id=casting.actorid
WHERE casting.actorid IN (SELECT  actorid FROM casting WHERE ord =1
GROUP BY actorid
HAVING COUNT(ord) >=15)
GROUP BY name

Upvotes: 0

rjose
rjose

Reputation: 625

This is a question in SQLZoo, Question # 13. The picture below explains the tables:

enter image description here

A little description about the database:

This database features two entities (movies and actors) in a many-to-many relation. Each entity has its own table. A third table, casting , is used to link them. The relationship is many-to-many because each film features many actors and each actor has appeared in many films.

Here is the answer I found working:

select A.name
from actor A
inner join casting C
on C.actorid = A.id
where  C.ord =1                    /*only the lead roles*/
group by A.id                      /*grouped by Actor ID*/
having count(C.movieid) >=15      /*at least 15 starring roles*/
order by A.name                  /* in alphabetical order*/

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

you can not use aggregation on where need having

select name, count(*)
from casting
join actor on actorid=actor.id
where ord=1  
and exists ( select 1 from casting
 having count(movieid)>=30)   
 group by actorid,name
 having count(movieid)>=30
 order by name

Upvotes: 1

forpas
forpas

Reputation: 164214

Join the tables, group by actor and put the condition in the having clause. 

select 
  a.name,
  sum(case c.ord when 1 then 1 else 0 end) starringroles
from actor a inner join casting c
on c.actorid = a.id
group by a.id, a.name
having sum(case c.ord when 1 then 1 else 0 end) >= 30
order by a.name

The expression sum(case c.ord when 1 then 1 else 0 end) will count the number of starring roles (with ord = 1).

Upvotes: 2

PeterHe
PeterHe

Reputation: 2766

select MAX(name) AS name, count(*) AS roles
from casting
join actor on actorid=actor.id
group by actorid
HAVING COUNT(*)>=30
order by name;

Upvotes: 0

Related Questions