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

Note that it's fantastically unlikely that a price would be recorded as double rather than decimal... and see normalisation — Strawberry
– Strawberry, Commented Dec 25, 2016 at 11:26

Gurwinder Singh · Accepted Answer · 2016-12-25 11:59:52Z

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)

edited Dec 25, 2016 at 11:59

answered Dec 25, 2016 at 11:24

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

anti-k Over a year ago

That is it!! Only I do not need sum, but I refactored it simply to my needs

Gurwinder Singh Over a year ago

Glad it helped.

K.Suthagar · Accepted Answer · 2016-12-26 02:04:12Z

0

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)

edited Dec 26, 2016 at 2:04

answered Dec 25, 2016 at 12:21

K.Suthagar

2,3061 gold badge18 silver badges28 bronze badges

4 Comments

anti-k Over a year ago

That is it, but you have mistyped in having clause - you used ORDERS.ORDERS (twice) ))

K.Suthagar Over a year ago

I have made a mistake, Thank you

Gurwinder Singh Over a year ago

@anti-k that's exact same query as mine

Gurwinder Singh Over a year ago

@K.Sathagar That's not much of a stretch. I think that much OP should be able to do himself.

Collectives™ on Stack Overflow

MYSQL query using JOIN table

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related