Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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.


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

Open in new window

0
twentythree
Asked:
twentythree
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
To query based on an aggregate (i.e. top 100 spenders, instead of top 100 spend records), use the HAVING clause instead of WHERE

SELECT TOP 100 Customer, Sum(Orders)
FROM YourTable
GROUP BY Customer
ORDER BY Sum(Orders) DESC

0
 
Louis01Commented:

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

Open in new window

0
 
twentythreeAuthor Commented:
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...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Louis01Commented:
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.
0
 
Anthony PerkinsCommented:
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
0
 
twentythreeAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
>>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.
0
 
Louis01Commented:
As I said: Exclude dbo.sent_items.date_order_placed
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now