LEFT JOIN needed? WHERE conditions behave similarly to INNER JOIN

Question

Trying to do a simple division of counts based on different criterion from the sample table, grouped by movie rating. I am using the sample SAKILA schema that comes by default with MySQL.

You can see the separate counts I'm expecting to see from these first two queries:

FIRST QUERY:

SELECT f1.rating, count(f1.title)
FROM sakila.film f1
WHERE f1.title like '%BA%'
GROUP BY rating
ORDER BY rating ASC

FIRST QUERY RESULTS:

    rating  count(f1.title)
    G       4
    PG      5
    PG-13   8
    R       6
    NC-17   6

SECOND QUERY:

SELECT f1.rating, count(f1.title)
FROM sakila.film f1
WHERE f1.title like '%AM%'
GROUP BY rating
ORDER BY rating ASC

SECOND QUERY RESULTS:

    rating  count(f1.title)
    G       8
    PG      6
    PG-13   9
    R       18
    NC-17   7

What I am after is for the G rating to have the division of 4/8, for the PG rating to have the division of 5/6, and so forth.

I had hoped the following query would accomplish this calculation, however to me the LEFT JOIN appears to be acting as an INNER JOIN because these two WHERE conditions only result in 1 movie that contains both 'BA' as well as 'AM', which is why the result is what it is:

PROBLEMATIC QUERY:

SELECT f1.rating, count(f1.title)/count(f2.title) 
FROM sakila.film f1
LEFT JOIN sakila.film f2
ON f1.film_id = f2.film_id
WHERE f1.title like '%BA%'
AND f2.title like '%AM%'
GROUP BY rating
ORDER BY rating ASC

PROBLEMATIC QUERY RESULTS:

    rating  count(f1.title)/count(f2.title) 
    PG-13   1.0000

How can I modify the problematic query to divide the counts from the first two queries as intended and group by rating? Do I need to rewrite the query using a UNION ALL, or can the LEFT JOIN still be used in this case?

Thanks in advance.

score 3 · Accepted Answer · answered Feb 09 '17 at 05:40

3

You can use conditional aggregation here:

SELECT rating,
       SUM(CASE WHEN title LIKE '%BA%' THEN 1 ELSE 0 END) /
       SUM(CASE WHEN title LIKE '%AM%' THEN 1 ELSE 0 END) AS the_ratio
FROM sakila.film
GROUP BY rating
ORDER BY rating ASC

Note that your current approach of doing a self-join I think is going in the wrong direction. Instead, we can just make a single scan of the entire table and count the various titles for each rating group.

answered Feb 09 '17 at 05:40

Tim Biegeleisen

502,043
27
286
360

Works great, thanks so much for the fast response and for the explanation – Sidney Carton Feb 09 '17 at 06:13
Hi Sidney, please open a new question instead of pasting here as a comment. – Tim Biegeleisen Feb 09 '17 at 06:35
Here it is, thanks: http://stackoverflow.com/questions/42130476/calculating-acceptance-ratio-with-left-join-and-self-join-and-aggregate-function – Sidney Carton Feb 09 '17 at 07:18

score 1 · Answer 2 · answered Feb 09 '17 at 05:46

You do not need any join.

SELECT 
  rating,
  count(if (title like '%BA%', 1, null)) / count(if (title like '%AM%', 1, null))
FROM sakila.film
GROUP BY 1
ORDER BY 1

Group by 1 / order by 1 means the first column in select

count will not count on any null values

LEFT JOIN needed? WHERE conditions behave similarly to INNER JOIN

2 Answers2