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 cannot think of a neat way of doing this. Anyone who can help me get started on this please?
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
) 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')
Write as:
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.
Assuming totalPurchases as total of Orders:
SELECT Customer_name, COUNT(o.order_id) as totalPurchases FROM Customer_t c
LEFT JOIN Order_t o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
Edit:
SELECT Customer_name, SUM(p.unity_price*ol.quantity) as totalPurchases FROM Customer_t c
LEFT JOIN Order_t o ON o.customer_id = c.customer_id
LEFT JOIN Order_Line_t ol ON ol.order_id = o.order_id
LEFT JOIN Product_t p ON p.product_id = ol.product_id
GROUP BY c.customer_id
THIS ANSWER WILL BE HELPFUL FOR YOU !!!!!!!!!!!!!!!!!!!
I did not have a mysql db installed in my device so I used the ONLINE TRY-IT-YOURSELF mysql editor at http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
As I used the TIY editor I could not change the table names but I could find similar table structure with what you have given
CustomerID || CustomerName .
CustomerID || OrderID .
OrderID || ProductID || Quantity .
ProductID || Price
with
table1 as (select o.CustomerID, o.orderid,
(
select sum(od.Quantity * p.Price) from orderdetails od, products p
where od.ProductID = p.ProductID and od.orderid = o.orderid group by od.orderid) as total
from orders o group by o.orderid, o.CustomerID )-- having o.CustomerID in(4,10))
select c.CustomerName,sum(t1.total) as total_purchases from table1 t1,customers c
where t1.customerid = c.customerid group by t1.customerid having total_purchases =
(with
table1 as (select o.CustomerID, o.orderid,
(
select sum(od.Quantity * p.Price) from orderdetails od, products p
where od.ProductID = p.ProductID and od.orderid = o.orderid group by od.orderid) as total
from orders o group by o.orderid, o.CustomerID )-- having o.CustomerID in(4,10))
select sum(t1.total) as total_purchases from table1 t1,customers c
where t1.customerid = c.customerid group by t1.customerid order by total_purchases desc
LIMIT 1)
This Query lists all the Customer Names & Purchase Amount of All customers with the Maximum Purchase
