1

I don't know to clearly say the problem but I'm trying to find the highest order of a particular item and match it to a customer. Here below is the table and the data.

This is Customers Table:

CUSTOMER_ID FIRST_NAME  LAST_NAME   CITY    COUNTRY PHONE
     1        Maria       Anders    Berlin  Germany 030-0074321
     2        Ana         Trujilo   México D.F. Mexico  (5) 555-4729
     3        Antonio     Moreno    México D.F. Mexico  (5) 555-3932
     4        Maria       Anders    London  UK  (171) 555-7788
     5        Christina   Berglund  Lulea   Sweden  0921-12 34 65

This is the Items table:

ITEM_ID   ITEM_NAME  STOCKS  PRICE
   1       Shoes       43    300
   2       Pants       25    500
   3       Shirts      64    150
   4       Bags        54    500
   5       Jackets     36    800

This is the Orders table:

ITEM_ID   CUSTOMER_ID   QUANTITY    DTTM
   1           1        3      3/2/2019
   1           2        2      3/2/2019
   1           2        4      3/5/2019
   3           5        1      3/1/2019
   5           3        2      3/6/2019
   4           4        3      3/7/2019
   2           2        5      3/8/2019
   2           1        2      3/9/2019

This is expected output:

ITEM_NAME   NUMBER_OF_ORDERS    MOST_ORDERS CUSTOMER_NAME   HIGHEST_PAYMENT
Shoes            3                      4    Ana Trujilo        1200
Pants            2                      5    Ana Trujilo        2500
Jackets          1                      2    Antonio Moreno     1600
Shirts           1                      1    Christina Berglund 150
Bags             1                      3    Maria Anders       1500

This is what I've gotten so far but instead of Ana Trujilo be the output twice it's Maria Anders and I ran out of ideas.

select item_name, Number_of_orders, Most_order, concat(first_name, " ", last_name) Customer_name, Highest_payment from customers
inner join
(
select item_name, orders.customer_id, count(orders.item_id) as Number_of_orders, max(quantity) as Most_order,
(price * max(quantity)) as Highest_payment from items
inner join orders on items.item_id=orders.item_id
inner join customers on customers.customer_id=orders.customer_id
group by item_name
) b on customers.customer_id=b.customer_id
order by 4
cj _com
  • 11
  • 2
  • highest order by number of items/order or sum payment. What would the output look like if two customers purchased the same amount of a particular item? Are you using MySQL-8.0+ or MariaDB-10.2+? – danblack May 16 '20 at 05:54
  • @danblack I'm using MySQL-8.0+ and the output must look like the one above. – cj _com May 16 '20 at 07:07

0 Answers0