MYSQL query using JOIN table

Question

I have two tables one CUSTOMERS

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID        | int(11)      | NO   | PRI | NULL    |       |
| FIRSTNAME | varchar(50)  | YES  |     | NULL    |       |
| LASTNAME  | varchar(50)  | YES  |     | NULL    |       |
| ADDRESS   | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

and orders

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| ID            | int(11)      | NO   | PRI | NULL    |       |
| PRODUCT_NAME  | varchar(100) | YES  |     | NULL    |       |
| PRODUCT_PRICE | double(10,4) | YES  |     | NULL    |       |
| DATE_ORDER    | date         | YES  |     | NULL    |       |
| ID_CUSTOMER   | int(11)      | YES  |     | NULL    |       |
| AMOUNT        | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

I need to "Get the first and last names of the customers who made orders in total sum greater than the average sum of all orders. Don’t care about duplicates".

Here what I've tried

 select FIRSTNAME, LASTNAME, ID, 
        AVG(PRODUCT_PRICE * AMOUNT)  
 from     CUSTOMERS C 
 join     ORDERS O 
 on       C.ID = ID_CUSTOMER 
 GROUP BY FIRSTNAME, LASTNAME 
 HAVING AVG(PRODUCT_PRICE * AMOUNT) < (
                 select (PRODUCT_PRICE * AMOUNT) 
                 from ORDERS 
                 where C.ID = O.ID_CUSTOMER;

That does not work. I need some help

Answer 1

Most part of your code is correct, you want just remove the part where C.ID = O.ID_CUSTOMER;. this is not need to calculate the average value of the order items.

using this following code you can get the required outut

SELECT FIRSTNAME,LASTNAME FROM CUSTOMERS 
INNER JOIN ORDERS ON CUSTOMERS.ID=ORDERS.ID_CUSTOMER 
GROUP BY CUSTOMERS.ID 
HAVING SUM(ORDERS.PRODUCT_PRICE*ORDERS.AMOUNT)>
(SELECT AVG(PRODUCT_PRICE*AMOUNT) FROM ORDERS)

Answer 2

You need to find average sum of all orders in a subquery.

select 
    C.ID,
    C.FIRSTNAME,
    C.LASTNAME,
    SUM(O.PRODUCT_PRICE * O.AMOUNT)
from     CUSTOMERS C 
join     ORDERS O 
on       C.ID = O.ID_CUSTOMER 
GROUP BY C.ID, C.FIRSTNAME, C.LASTNAME 
HAVING SUM(PRODUCT_PRICE * AMOUNT) > (
                 select AVG(PRODUCT_PRICE * AMOUNT)
                 from ORDERS)