CODEWITHSUNDEEP
sql
Joshua Fang
Joshua Fang

Reputation: 11

how to use View function in SQL

I have a question for an assignment and it is listed below here.

"List a query that list the film genres and gross revenue for that genre, conditional to the gross revenue for that genre being higher than average gross revenue per genre. Hint: Use a View to simplify the query."

My code is as follows:

CREATE VIEW grossrevenue as (
  SELECT category.name, SUM(payment.amount) as sumpay
  FROM payment
  JOIN rental ON payment.rental_id = rental.rental_id
  JOIN inventory ON rental.inventory_id = inventory.inventory_id
  JOIN film_category ON inventory.film_id = film_category.film_id
  JOIN category ON film_category.category_id = category.category_id
  GROUP by category.name
);

Then:

Select sumpay
FROM grossrevenue
WHERE sumpay > AVG(sumpay);

What is wrong with it and why is it not running?

Upvotes: 1

Views: 94

Answers (1)

John Humphreys
John Humphreys

Reputation: 39304

Syntax wise, AVG() does not make sense without a group of records. It’s for aggregating multiple rows.

Try putting a sub query to calculate the average, or rethink your view.

Requested Edits

People generally shouldn't give answers to school assignments, so I didn't dig into your view SQL too much, and my answer was somewhat vague on purpose.

But here's some info to clarify my above answer so you can go the right direction:

Select sumpay
FROM grossrevenue
WHERE sumpay > AVG(sumpay);

You can't do sumpay > AVG(sumpay) as they both operate on just one row. AVG() of one row doesn't make sense.

If AVG(sumpay) was the only thing in the select clause like this, then it makes sense as it will average all the rows and give you literally just the average.

Select AVG(sumpay)
FROM grossrevenue

But in your where clause, its literally just operating on the current row and doesn't mean anything. There is nothing to average; that's not how it works.

You can use a "subquery" to get the average (literally replace AVG(sumpay) with a subquery after googling it). That would be one option.

Upvotes: 1

Related Questions