My relationship schema looks something like this :
Now I am trying to write a SQL query to find the customer who has the highest total amount of purchases in the year 2016. I want to know the customer name and the total amount of purchases.
Select Customer_name, TotalAmountOfAllHisPurchases from ...
I believe the following query should work to identify the customer name with the highest amount derived from all orders in the current calendar year:
SELECT CUSTOMER_NAME, Y.QNTY FROM CUSTOMER_T CUST, ( SELECT X.CUSTOMER_ID, X.QNTY, MAX(X.QNTY) MAXAMT FROM ( SELECT ORD.CUSTOMER_ID, SUM(OLN.QUANTITY * PRD.UNIT_PRICE) QNTY FROM ORDER_T ORD, ORDER_LINE_T OLN, PRODUCT_T PRD WHERE TRUNC(OLN.ORDER_DATE,'YEAR') = TRUNC(SYSDATE,'YEAR') AND ORD.ORDER_ID = OLN.ORDER_ID AND PRD.PRODUCT_ID = OLN.PRODUCT_ID GROUP BY ORD.CUSTOMER_ID ORDER BY SUM(OLN.QUANTITY) DESC ) X ) Y WHERE CUST.CUSTOMER_ID = Y.CUSTOMR_ID AND Y.QNTY = Y.MAXAMT;
The inner-most query joins the ORDER, ORDER_LINE, and PRODUCT tables grouping by the customer ID to sum the total purchases for the current year (orders by summed amount descending). The query up one level uses the inner query results and adds on the max summed total of purchases. The outer-most query joins the CUSTOMER_T table with the inner results to get the customer Name and total order amount for all customers that match the MAXAMT value.
You may modify the date condition to always restrict to 2016 (regardless of current year) as follows:
WHERE TRUNC(OLN.ORDER_DATE,'YEAR') = TRUNC(SYSDATE,'YEAR')
WHERE TRUNC(OLN.ORDER_DATE,'YEAR') = TRUNC(TO_DATE('01/01/2016','MM/DD/YYYY'),'YEAR')
You may also pull the outer query of CUSTOMER_T inside the inner table and add it to the joins. It accomplishes the same thing, but I'm not sure which is more efficient (which is applicable if your data set is large).
Note: Written from an Oracle DB perspective, hopefully this helps and you are able to convert the syntax.
Edit: I realized my suggestion would overlook multiple customers with the same total spending amount. I've reworked it to use MAX and show multiple customers (if applicable). Hope this works for your/can be converted to MYSQL.