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?
Advertisement
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 :-