CODEWITHSUNDEEP
sqljoingroup-bysubquery
coryriegel98
coryriegel98

Reputation: 27

Struggling with subquery & joins

I'm working on a problem set for my data class and I keep getting the 'missing expression' error code. Here is what I've written:

select c.numbkids "Number of Kids",
       (select avg(r.rating)
        from netflix.ratings100 r join netflix.movies_genres m on r.movieid = m.movieid
        where m.genrecode like 'ACT'),
       (select avg(r.rating)
        from netflix.ratings100 r join netflix.movies_genres m on r.movieid = m.movieid
        where m.genrecode like 'ADV'),
       (select avg(r.rating)
        from netflix.ratings100 r join netflix.movies_genres m on r.movieid = m.movieid
        where m.genrecode like 'COM'),
       (select avg(r.rating)
        from netflix.ratings100 r join netflix.movies_genres m on r.movieid = m.movieid
        where m.genrecode like 'MYS')
from netflix.customers c join netflix.ratings100 r on c.custid = r.custid
where c.numbkids < 4 and
group by c.numbkids

I am displaying families with only 3 or fewer kids (column 1), and I need to display the average rating that each family size has given the action genre (column 2), adventure genre (column 3), comedy genre (column 4), and mystery genre (column 5). It seems to me like I need to use subqueries to group each of those genre columns by number of kids before taking their average, but I don't know how to do that without adding additional columns. Please help!

Upvotes: 1

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You are correct. You can use conditional aggregation, which in the standard uses FILTER, but in most databases uses CASE:

select c.numbkids "Number of Kids",
       avg(case when m.genrecode like 'ACT' then r.rating end),
       avg(case when m.genrecode like 'ADV' then r.rating end),
       avg(case when m.genrecode like 'COM' then r.rating end),
       avg(case when m.genrecode like 'MYS' then r.rating end)
from netflix.customers c join
     netflix.ratings100 r
     on c.custid = r.custid join
     netflix.movies_genres m
     on r.movieid = m.movieid
where c.numbkids < 4
group by c.numbkids

Upvotes: 1

Related Questions