SHARE
TWEET

Untitled

a guest Apr 24th, 2019 107 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT T1.customerNumber, customerName, IFNULL(T1.productCode, 'N/A') productCode, IFNULL(T1.moneySpent, 'N/A') moneySpent
  2. FROM
  3.     (SELECT C.customerNumber, customerName, productCode, SUM(priceEach*quantityOrdered) as moneySpent
  4.     FROM customers C
  5.     LEFT JOIN orders O USING(customerNumber)
  6.     LEFT JOIN orderdetails OD ON O.orderNumber = OD.orderNumber
  7.     GROUP BY C.customerNumber, productCode) as T1
  8. LEFT JOIN
  9.     (SELECT customerNumber, MAX(moneySpent) as moneySpent
  10.      FROM
  11.         (SELECT C.customerNumber, customerName, productCode, SUM(priceEach*quantityOrdered) as moneySpent
  12.         FROM customers C
  13.         LEFT JOIN orders O USING(customerNumber)
  14.         LEFT JOIN orderdetails OD ON O.orderNumber = OD.orderNumber
  15.         GROUP BY C.customerNumber, productCode) tmp
  16.      GROUP BY customerNumber
  17.      ) AS T2
  18. ON T1.customerNumber = T2.customerNumber and T1.moneySpent = T2.moneySpent
  19. WHERE NOT (T1.moneySpent is not NULL and T2.moneySpent is NULL)
  20. ORDER BY customerNumber
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top