MySQL: Find out which store customer spent most money in.

Question

Making an automated call list to be generated based on certain search criteria that pulls customers names and phone numbers.

There are 4 tables: Customer, Phone_Numbers, Sales_Header, Sales_Detail.

The query is as follows:

SELECT CONCAT(customer.First_Name, ‘’, customer.Last_Name), 
       Phone_Numbers.Number, 
       Customer.ID 
FROM Sales_Header 
INNER JOIN Sales_Detail ON Sales_Detail.Header_ID = Sales_Header.ID 
INNER JOIN Customer ON Customer.ID = Sales_Header.Customer_ID 
INNER JOIN Phone_Numbers ON Phone_Numbers.Customer_ID = Customer.ID 
WHERE Sales_Header.WebSale = -1 
  AND Sales_Detail.Price > 1000 
  AND DateSold > 20081002

Question:

Each sale in the sales_header has a store location (ID for which store it was sold at). However customers can buy from more than one store. I need to find out which store the customer spent the most money in, and add that store to the select statement output above.

Answer 1

Try using SUM, GROUP BY, and ORDER BY ... does that do the trick?

SELECT CONCAT(customer.First_Name, ‘’, customer.Last_Name), 
       Phone_Numbers.Number, 
       Customer.ID,
       SUM(Sales_Detail.Price) AS Total_Purchases
FROM Sales_Header 
INNER JOIN Sales_Detail ON Sales_Detail.Header_ID = Sales_Header.ID 
INNER JOIN Customer ON Customer.ID = Sales_Header.Customer_ID 
INNER JOIN Phone_Numbers ON Phone_Numbers.Customer_ID = Customer.ID 
WHERE Sales_Header.WebSale = -1 
  AND Sales_Detail.Price > 1000 
  AND DateSold > 20081002
GROUP BY Customer.ID
ORDER BY Total_Purchases

Answer 2

If you're using MySQL, I believe it's:

SELECT CONCAT(customer.First_Name, ‘’, customer.Last_Name), 
       Phone_Numbers.Number, 
       Customer.ID, Sales_Detail.ID, Sum(Sales_Detail.Price) 
FROM Sales_Header 
INNER JOIN Sales_Detail ON Sales_Detail.Header_ID = Sales_Header.ID 
INNER JOIN Customer ON Customer.ID = Sales_Header.Customer_ID 
INNER JOIN Phone_Numbers ON Phone_Numbers.Customer_ID = Customer.ID 
WHERE Sales_Header.WebSale = -1 
  AND Sales_Detail.Price > 1000 
  AND DateSold > 20081002
GROUP BY CONCAT(customer.First_Name, ‘’, customer.Last_Name), 
       Phone_Numbers.Number, 
       Customer.ID, Sales_Detail.ID
ORDER BY Sum(Sales_Detail.Price) Desc Limit 1;

In SQL Server, you'd do "top 1" before Concat.

Also, depending on how your tables lay out, you'll also want to put a "distinct" (no parentheses or commas before "concat", to remove duplicates.