Geek Answers Handbook

Sql: join and group by

Having 3 tables:

film (id, title, yr, rating, votes, director) actor (id, name) casting (filmid, actorid, ord)

Q: What were the busiest years for John Travolta. Show the number of films he made for each year.

A: My attempt has been syntactically deprecated. 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'
+4
source share
3 answers
  • You are missing the name of the second table after join
  • use where not having

Try the following:

 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 pay attention to the sequential formatting that I used. If you use a good format, it’s easier to find syntax errors

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

+6
source

Remove ( ) from 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 having to where , because havings are used for aggregate functions in connections to 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
+1
source

To join u, you must specify a connection to the table, there must be

 join movie on movie.id = casting.movieid
0
source

More articles:


All Articles