SQL; Error Code: 1111 | needing to encapsulate AVG

Question

I have the following problem:

1) I have successfully managed to create a result displaying JUST the revenues of multiple movies (count how often it was rented * the rental rate). [Example A]

2) Now I want to get the average of this. I have managed to do it if I write another query around it [Example B], but I want to enclose it into the same query if possible. [Example C - which gives me an error]. Is this at all possible?

Example A:

SELECT  COUNT(r.rental_id) * f.rental_rate AS Revenue
        FROM film f, rental r, inventory i
        WHERE i.inventory_id = r.inventory_id
            AND f.film_id = i.film_id
        GROUP BY f.title
        ORDER BY Revenue DESC

Example B: (How I could make it work - encapsulated query)

SELECT AVG(B.Revenue)
    FROM (

        SELECT  COUNT(r.rental_id) * f.rental_rate AS Revenue
        FROM film f, rental r, inventory i
        WHERE i.inventory_id = r.inventory_id
            AND f.film_id = i.film_id
        GROUP BY f.title
        ORDER BY Revenue DESC ) B

Example C: (How i want it to work - one single query)

SELECT  AVG (COUNT(r.rental_id) * f.rental_rate) AS Revenue
        FROM film f, rental r, inventory i
        WHERE i.inventory_id = r.inventory_id
            AND f.film_id = i.film_id
        GROUP BY f.title
        ORDER BY Revenue DESC

Example C gives: "Error Code: 1111 \n Invalid use of group function"

Never use commas in the `FROM` clause. *Always* use explicit, proper, `JOIN` syntax. — Gordon Linoff, Dec 06 '16 at 12:49
I actually looked that up an hour ago and found this post. http://stackoverflow.com/questions/2241991/in-mysql-queries-why-use-join-instead-of-where — Cribber, Dec 06 '16 at 12:51
You should rename the title, this has nothing to do with SVG, rather partially include the error message itself. — Daniel W., Dec 06 '16 at 12:51

score 0 · Accepted Answer · answered Dec 06 '16 at 12:56

Look at the error message carefully Invalid use of group function. Problem is in first line of your query as pointed below. You can't nest the group function the way you are trying. You will have to get that in a outer query.

AVG (COUNT(r.rental_id) * f.rental_rate)

Something like

SELECT AVG(Revenue) FROM (
SELECT  COUNT(r.rental_id) * f.rental_rate AS Revenue
        FROM film f
        JOIN inventory i ON f.film_id = i.film_id
        JOIN rental r ON i.inventory_id = r.inventory_id
        GROUP BY f.title ) xxx
        ORDER BY Revenue DESC;

SQL; Error Code: 1111 | needing to encapsulate AVG

1 Answers1