Inner joins with Case statement

Question

Instructions

Write a query to return the number of productive and less-productive actors. The order of your results doesn't matter.

Definitions

productive: appeared in >= 30 films. less-productive: appeared in <30 films.

I got this error on my query below

(syntax error at or near "AS" LINE 14: END) AS actor_category ^)

SELECT a.actor_id, MAX(a.first_name), MAX(a.last_name)
FROM actor a
INNER JOIN film_actor fa
ON a.actor_id = fa.actor_id
INNER JOIN film f
ON fa.film_id = f.film_id
    (CASE
        WHEN a.actor_id >= 30 THEN 'productive'
        WHEN a.actor_id <= 30 THEN 'less-productive'
    END) AS actor_category
GROUP BY a.actor_id;

This was the answer I was given:

SELECT actor_category,
    COUNT(*)
FROM (        
    SELECT 
        A.actor_id,
        CASE WHEN  COUNT(DISTINCT FA.film_id) >= 30 THEN 'productive' ELSE 'less productive' END AS actor_category       
    FROM actor A
    LEFT JOIN film_actor FA
    ON FA.actor_id = A.actor_id
    GROUP BY A.actor_id
) X
GROUP BY actor_category;

Why does it have to be done this way?

you can't just throw a case statement in the join conditions like that without comparing it to something, and you can only alias an expression in the select clause — Kurt, Dec 19 '22 at 22:07
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Dec 20 '22 at 00:21
[CROSS JOIN vs INNER JOIN in SQL](https://stackoverflow.com/a/25957600/3404097) [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) PS You really need to read a (good) introduction to relational querying & SQL. See Darwen's free books at bookboon.com. — philipxy, Dec 20 '22 at 00:30
Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Dec 20 '22 at 00:30
It is not useful to give the part of an error message that points to a character in a line using a carat unless you line up the error message & the erroneous line/character. PS Please read the editor "?" help & advanced help re formatting code & quotes inline & in blocks. — philipxy, Dec 20 '22 at 00:51

score 0 · Answer 1 · answered Dec 20 '22 at 03:24

Plenty of SQL learning documents, but sometimes having a task and understanding that one can help you learn APPLIED techniques vs just here is how to write a query.

First, what do you care about. The total number of movies ANY given actor has been in. In this case, you have the film_actor table. Get your counts from this first.

select
      fa.actor_id,
      count(*) TotalMovies
   from
      film_actor fa
   group by
      fa.actor_id

The result of this might have

actor_id  TotalMovies
1         7
2         34
3         27
4         41

In this case, you dont care WHO was in the movie, just that an actor was in X number of movies. So now that you have THIS query, you can then join to the actual actors table to pull their names and THEN apply the case/when based on the total movies count.

select
      a.FirstName,
      a.LastName,
      PreQueryCnts.TotalMovies,
      -- NOW we can apply the case/test
      case when PreQueryCnts.TotalMovies >= 30
           then 'productive'
           else 'less productive' end as Actor_Category
   from
      (select
             fa.actor_id,
             count(*) TotalMovies
          from
             film_actor fa
          group by
             fa.actor_id ) PreQueryCnts
         JOIN actor a
            on PreQueryCnts.actor_id = a.actor_id

Although this does not specifically answer your question, you can see how you could get the detail. Now, to get your category total, just use the result of the original query and the simplify the count at the outer level too.

select
      -- NOW we can apply the case/test
      case when PreQueryCnts.TotalMovies >= 30
           then 'productive'
           else 'less productive' end as Actor_Category,
      count(*) as ActorsInThisCategory
   from
      (select
             fa.actor_id,
             count(*) TotalMovies
          from
             film_actor fa
          group by
             fa.actor_id ) PreQueryCnts
   group by
      case when PreQueryCnts.TotalMovies >= 30
           then 'productive'
           else 'less productive' end

But there are many ways to pull a query. As long as you understand how/where the components come from. Notice the final query does not even care WHO the actor is and is not part of the query at all.

Inner joins with Case statement

1 Answers1