CODEWITHSUNDEEP
sql
Elad Benda
Elad Benda

Reputation: 36654

sql : join and group by

Having 3 tables:

movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord)

Q:Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.

A: My try is syntactically worng. why ?

select yr, count(*)
from 

(actor join casting
on (actor.id = casting.actorid)

join 
on (movie.id = casting.movieid)

group by yr 
having actor.name='John Travolta'

Upvotes: 4

Views: 653

Answers (3)

Devin Burke
Devin Burke

Reputation: 13820

Remove the ( ) from around the table name and add movie to your second join.

select yr, count(*)
from actor join
    casting on actor.id = casting.actorid join
    movie on movie.id = casting.movieid
group by yr
having actor.name='John Travolta'

EDIT:

You need to switch your having to a where because havings are use for aggregate functions in conjunctions with your group by.

select yr, count(*)
from actor join
    casting on actor.id = casting.actorid join
    movie on movie.id = casting.movieid
where actor.name = 'John Travolta'
group by yr

Upvotes: 1

Piotr Auguscik
Piotr Auguscik

Reputation: 3681

To join u have to specify table ure joining, should be

join  movie
 on movie.id = casting.movieid

Upvotes: 0

Bohemian
Bohemian

Reputation: 425023

  • You are missing the second table name after join
  • use where not having

Try this:

select yr, count(*)
from actor
join casting on actor.id = casting.actorid
join movie on movie.id = casting.movieid -- you were missing table name "movie"
where actor.name='John Travolta' -- "where", not "having"
group by yr

Also note the consistent formatting I used. If you use a good format, it's easier to find syntax errors

FYI, having is used for aggregate functions, eg having count(*) > 3

Upvotes: 6

Related Questions