What's the best (fastest to execute) way to handle this?

[aspdevguy]

I need to put together a report that will pull data from the following tables: Customers, Orders.

The Customers table contains about 11,000 customer records and the Orders table contains about 40,000 order records.

What I need to do is basically pull a total number of orders and the total revenue per customer for a given date range.

The way it works now is it lists all customers and then it gets a COUNT for all orders for that particular customer and then SUM of their totals on each line. So I basically first query the Customers table, then Loop through the entire recordset while pulling COUNT and SUM for each line.

Example:
John Doe ABC Company 3 $350.00
Jane Doe XYZ Company 5 $900.00

The way it's written right now takes an enormously long amount of time and probably server resources. Does anyone know of a better, faster way to pull all this data into a report using ASP and HTML? Or is there another alternative?

Thanks in advance!

[Nicodemas]

If we assume your tables look like this (or similar):

Table 'customers'                Table 'orders'
====================             ============================ 
custID | custName                ordID | ordPrice | custID
====================             ============================ 
1           John                 1       15          5
1           Bill                 2       10          4
1           Jake                 3       200         4
1           Jimmy                4       220         4
1           Lane                 5       10          3
                                 6       12          3
                                 7       5           2
                                 8       6           5
                                 9       110         1
                                 10      100         1
 

Your query should appear something like this:

SELECT c.custID, c.custName, SUM(ordPrice) as sumTotal, COUNT(ordID) as cntOrders FROM customers c left join orders o on c.custID = o.custID GROUP BY c.custID, c.custName

Which would produce results like:

==============================================
custID | custName | sumTotal | cntOrders
==============================================
1         John        210         2
1         Bill        5           1
1         John        22          2
1         John        430         3
1         John        21          2
 

[aspdevguy]

Sorry I should have posted the table structure...

The tables look like this:


Table 'customers'
=============== =============== =============== =======
CustID | CustLName | CustFName | CustCompany
=============== =============== =============== =======
1 John Smith ABC Company
2 Jane Doe XYZ Company
3 Jake Smith
4 John Johnson Anything Inc


Table 'orders'
=============== =============== =====
OrderID | CustID | OrderTotal
=============== =============== =====
1 1 25
2 1 45
3 2 15
4 3 18
5 3 23
6 3 75
7 2 40
8 2 25
9 4 105


I need to sort the results by CustLName, CustFname, CustCompany and then display it in this format:

=============== =============== =============== =============
CustLName, CustFName | custCompany | sumTotal | cntOrders
=============== =============== =============== =============
Smith, John ABC Company 70.00 2
Doe, Jane XYZ Company 80.00 3
Smith, Jake 116.00 3
Johnson, John Anything Inc 105.00 1

[aspdevguy]

Actually I figured it out myself. Here is the SELECT statement in case anybody ever needs it.

SELECT c.CustID, c.CustLName, c.CustFName, c.CustCompany, SUM(OrderTotal) as sumTotal, COUNT(OrderID) as cntOrders FROM Customer c LEFT JOIN Orders o on c.CustID = o.CustID WHERE "& MY_SQL_QUERY &" GROUP BY c.CustCompany, c.CustLName, c.CustFName, c.CustID ORDER BY c.CustCompany, c.CustLName, c.CustFName