Query that will result in a list of customer names and the average order value made by each customer

I have a database and I need to create a query that will retrieve a list of customer names and the average order value made by each customer. I tried:

SELECT c.customer_name, AVG(COUNT(o.order_id)*f.price) AS 'avgorderprice' 
FROM Customers c 
LEFT JOIN Orders o ON o.customer_id = c.customer_id 
INNER JOIN F_in_Or fio ON o.order_id = fio.order_id 
INNER JOIN Films f ON fio.film_id = f.film_id; 

This is my database structure:

But I get an error, what can be wrong?

Answer

But I get an error, what can be wrong ?

You are trying to use an aggregate function Count within an aggregate function and getting a MISUSE OF AN AGGREGATE FUNCTION.

i.e. – > misuse of aggregate function COUNT()

Additionally you are not GROUP‘ing your results and thus will receive the entire average rather than a per-customer average.

• Aggregate functions work on a GROUP the default being all unless a GROUP BY clause places the rows into GROUPS.

You could instead of AVG(COUNT(o.order_id)*f.price) use sum(f.price) / count(*). However, there is an average aggregate function avg. So avg(f.price)’ is simpler.

Additionally as you want an average per customer you want to use a GROUP BY c.customer clause.

Thus you could use :-

SELECT 
    c.customer_name, 
    avg(f.price) AS 'avgorderprice' --<<<<< CHANGED
    FROM Customers c 
    LEFT JOIN Orders o ON o.customer_id = c.customer_id 
    INNER JOIN F_in_Or fio ON o.order_id = fio.order_id 
    INNER JOIN Films f ON fio.film_id = f.film_id 
    GROUP BY c.customer_name --<<<<< ADDED
;

This would result in something like :-