twentythree
asked on
Top 100 spending customers
I'm currently try to build a page that will query the top 100 customers that have spent the most money between two dates that we enter in a form field. I've tried lots of different ways to get it to work but it just doesn't produce the numbers that I'm looking for :( The closest I've been able to get is where it pulls up the sum for the highest SINGLE order, but it doesn't seem to combine the orders by customer_ID.
I have two tables, one with all of the customer information, and one with the order information. Also it won't return 100 top rows even though it's stated to do so.
I have two tables, one with all of the customer information, and one with the order information. Also it won't return 100 top rows even though it's stated to do so.
SELECT TOP (100) SUM(dbo.TBL_OrderSummary.qty * dbo.TBL_OrderSummary.item_price) AS tot, dbo.sent_items.customer_ID, dbo.sent_items.customer_first,
dbo.sent_items.customer_last, dbo.sent_items.city, dbo.sent_items.state, dbo.sent_items.zip, dbo.sent_items.country, dbo.sent_items.email,
dbo.sent_items.phone, dbo.sent_items.address, dbo.sent_items.address2, dbo.sent_items.company, dbo.sent_items.date_order_placed,
dbo.sent_items.province
FROM dbo.TBL_OrderSummary INNER JOIN
dbo.sent_items ON dbo.TBL_OrderSummary.InvoiceID = dbo.sent_items.ID
WHERE (dbo.sent_items.shipped = N'Shipped')
GROUP BY dbo.sent_items.customer_ID, dbo.sent_items.customer_first, dbo.sent_items.customer_last, dbo.sent_items.city, dbo.sent_items.state,
dbo.sent_items.zip, dbo.sent_items.country, dbo.sent_items.email, dbo.sent_items.phone, dbo.sent_items.address, dbo.sent_items.address2,
dbo.sent_items.company, dbo.sent_items.date_order_placed, dbo.sent_items.province
ORDER BY tot DESC
SELECT TOP 100 * FROM (
SELECT SUM(dbo.TBL_OrderSummary.qty * dbo.TBL_OrderSummary.item_price) AS tot, dbo.sent_items.customer_ID, dbo.sent_items.customer_first,
dbo.sent_items.customer_last, dbo.sent_items.city, dbo.sent_items.state, dbo.sent_items.zip, dbo.sent_items.country, dbo.sent_items.email,
dbo.sent_items.phone, dbo.sent_items.address, dbo.sent_items.address2, dbo.sent_items.company, dbo.sent_items.date_order_placed,
dbo.sent_items.province
FROM dbo.TBL_OrderSummary INNER JOIN
dbo.sent_items ON dbo.TBL_OrderSummary.InvoiceID = dbo.sent_items.ID
WHERE (dbo.sent_items.shipped = N'Shipped')
GROUP BY dbo.sent_items.customer_ID, dbo.sent_items.customer_first, dbo.sent_items.customer_last, dbo.sent_items.city, dbo.sent_items.state,
dbo.sent_items.zip, dbo.sent_items.country, dbo.sent_items.email, dbo.sent_items.phone, dbo.sent_items.address, dbo.sent_items.address2,
dbo.sent_items.company, dbo.sent_items.date_order_placed, dbo.sent_items.province
) t1
ORDER BY tot DESC
ASKER
This is outputting the same data as the one I had posted before. Basically it's not combining the sum of all the orders by customer_ID. It just seems to be pulling single large orders when what I'm trying to do is compile them by customer. Plus, for some odd reason, it never outputs exactly the top 100. Most of the time it's less than 20...
Does removing the following columns from the SELECT and GROUP BY clauses make a difference?
dbo.sent_items.customer_ID
dbo.sent_items.date_order_ placed
Remove all columns you do not really need.
dbo.sent_items.customer_ID
dbo.sent_items.date_order_
Remove all columns you do not really need.
First. Does this produce the right results:
SELECT TOP 100
s.customer_ID,
SUM(o.qty * o.item_price) tot
FROM dbo.sent_items s
INNER JOIN dbo.TBL_OrderSummary o ON s.ID = o.InvoiceID
WHERE s.shipped = N'Shipped'
GROUP BY
s.customer_ID
ORDER BY
tot DESC
SELECT TOP 100
s.customer_ID,
SUM(o.qty * o.item_price) tot
FROM dbo.sent_items s
INNER JOIN dbo.TBL_OrderSummary o ON s.ID = o.InvoiceID
WHERE s.shipped = N'Shipped'
GROUP BY
s.customer_ID
ORDER BY
tot DESC
ASKER
The problem that I seem to be having is the GROUP BY clause. I do need all the other fields to output like street, date_order_placed, etc but those seem to conflict with the other information.
>>I do need all the other fields to output like street, date_order_placed, etc but those seem to conflict with the other information.<<
Exactly. Now does the query I posted previously give you the right number of rows. I need to know that answer, before I can proceed to contribute any valid solution.
Exactly. Now does the query I posted previously give you the right number of rows. I need to know that answer, before I can proceed to contribute any valid solution.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT TOP 100 Customer, Sum(Orders)
FROM YourTable
GROUP BY Customer
ORDER BY Sum(Orders) DESC