Trouble properly aggregating data with SQL

Question

Im currently working on the second part of a two part exercise and am having trouble figuring out how to show the average number of films per state from a dvd rentals database. The question I am trying to address is:

For each US state, show the average number of G-rated films that each customer has rented, in descending order. You only should provide one value for each state.

I currently have

WITH cte AS

    (
     SELECT COUNT(DISTINCT(rental_id)) AS filmcount
     FROM rental
     )
SELECT DISTINCT(district), AVG(filmcount) AS avg_film /*COUNT(DISTINCT(rental_id)) */
FROM  cte, customer 

LEFT OUTER JOIN address
ON customer.address_id = address.address_id  /*Using parent keys to join with foreign keys from relevant tables. we are focusing on customer rentals so this is our starting basis*/

LEFT OUTER JOIN rental
ON customer.customer_id = rental.customer_id

LEFT OUTER JOIN inventory
ON rental.inventory_id = inventory.inventory_id

LEFT OUTER JOIN film
ON inventory.film_id = film.film_id

LEFT OUTER JOIN city
ON address.city_id = city.city_id

WHERE return_date IS NOT NULL
AND rating = 'G'
AND country_id = 103
GROUP BY district;

with my results being enter image description here This is the before table enter image description here

Customer	Address	Rental	Inventory
customer_id	address_id	rental_id	inventory_id
store_id	address	rental_date	film_id
first_name	address2	inventory_id	store_id
last_name	district	customer_id	last_update
email	city_id	return_date
address_id	postal_code	staff_id
activebool	phone	last_update
create_date	last_update
last_update
active

Film	City
film_id	city_id
title	city
description	country_id
release_year	last_update
language_id
original_language_id
rental_duration
rental_rate
length
replacement_cost
rating
last_update
special_features
fulltext

How can I get the average count of unique ID per state or in this case district? Please help :)

Hi, Maybe you could show what you table looks like before operations. And also check out https://stackoverflow.com/help/how-to-ask — Nohman, Jun 05 '22 at 23:20
Thank you for the tips. I've added a picture of the before table — noelfry rodriguez, Jun 06 '22 at 00:33
Not my downvote, but posting pictures of technical information like code, sample data, or results, instead of formatted text, is the fastest way I've seen here for a question to be downvoted, closed, and ignored. — Joel Coehoorn, Jun 06 '22 at 01:03
The edit of posting your code instead of image is a great start. You need to list the table structures... what columns are within each respective table to show relationships. Without known column in respective tables will be harder to actually answer. — DRapp, Jun 06 '22 at 01:18

score 0 · Answer 1 · answered Jun 06 '22 at 01:12

your syntax is old style when you are doing comma list of tables. This is creating a Cartesian result thus throwing off your totals. For every record in your rental table is being joined to EVERY record in the address table.. Similarly when you use the CTE context to the customer table.

Please EDIT your post and provide sample table structures of pertinent columns for your rental, address, customer, inventory, film and city tables.

Trouble properly aggregating data with SQL

1 Answers1