Can't solve a problem using group by function

Question

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.

Answer 1

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).