Avoiding aggregation using stored procedures

I have a dynamically driven site that uses an sql server database.
I am trying to use the following sql in a recordset to calculate data specific
to each product for a particular customer:

SELECT (((100 - RM) * SELLINGPRICE) * TONNESPERANNUM) / 100 AS
ANNUALCONTRIBUTION
FROM product
WHERE CUSTID = MMColParam

MMColParam = Request.QueryString=("CUSTID")

The trouble I'm having is that the sql is subtotalling all the data in each
column. I've been told that there is a way around this using stored procedures
but I have no idea where to start!

Please help!

Cubanallstar.

I'm not sure what you mean. Can you explain it differently? What you have
will calculate a single value (ANNUALCONTRIBUTION) for every product, then
return all such values associated with a given customer. How does that differ
from what you want?

I have 3 pages;
Customer Overview - this provides a client overview with subtotals of product
data Options on this page to add new customer
Customer detail - this page contains a list of products for each customer that
is under development Options on this page to add new products
Product detail - on clicking on a product in the customer detail page this
shows the inividual product in question and gives the opportunity to edit or
delete

I have three tables in the database; a users table for logins, a cust_overview
table with CUSTID and CUSTOMER and a products table with CUSTID, PRODID,
PRODUCTREFERENCE, TONNESPERANNUM, SELLINGPRICE, RM etc

On customer detail and product detail the add and edit function share the same
form design. The end user enters data for productreference, tonnesperannum,
selling price and raw material (RM)%. These figures are stored on an sql server
database and from them I have created recordsets that calculate ADDED VALUE,
TURNOVERPERANNUM and ANNUAL CONTRIBUTION.

The recordsets work fine on the product detail page because they pass the
"Request.QueryString("PRODID") parameter but they dont work on the customer
detail page because the variable isnt passed on the url.

In the code on the above post, which is one of the calculations for the
customerdetail page, If i change the Request.QueryString to ("PRODID") which
works on the product detail page it doesnt work because there isnt a PRODID on
the url to request.

I hope my description makes sense!!

So is the problem that when there is no product Id there are no results? Is the desired behavior to find all products or a specific product or set of products?

Greg

I think I know what you're saying....
You're passing PRODID on the URL to a Product Detail page. When you submit a
form on the product detail page, your processing page isn't working because the
PRODID is no longer available.

If that's the case, just create a hidden field in your form called PRODID with
a value of Request.QueryString("PRODID"). On your processing page, you can use
Request.Form("PRODID") as your filter.

Let me know if I misunderstood.

Im so bad at describing, I'm sorry!

Basically I am trying to run some calculations in SQL. For example:

On the cust detail page (2nd page)
A = Tonnes Per Annum (Entered)
B = Selling Price (Entered)
C = Raw Material % (Entered)
D = (100 - C) * B
E = A * B
F = A * D

On the customer overview page (1st page)
G = Sum of E
I = Sum of F
H = (Sum of E - Sum of F) / Sum of E

Initially the end user had to enter all the details including the calculations
on a form when adding a new product. I want to minimise this by only needing to
add A, B, C and for the rest to be calculated. I have been able to do this, as
mentioned above, but only for the "product detail" page where the PRODID
parameter is passed. I cant get it to work on "CustDetail" because the list
contains products each with different PRODID's so no parameter is passed.
Either that or my SQL is wrong!

Similarly on the customer overview page (Page1) I can get the totals for G and
I fine but I cant calculate Raw Material % without it adding (aggregating) all
the data for a specific CUSTID.

For Example
(G) (H) (I)
CUSTOMER SALES RM ANNUALCONTRIBUTION

sample 215,000 107,000

Cuban Allstar,

It sounds like you want to find the products for a specific customer and then
be able display the values of A-I for each prodcut ID.

Where you have this
SELECT (((100 - RM) * SELLINGPRICE) * TONNESPERANNUM) / 100 AS
ANNUALCONTRIBUTION
FROM product
WHERE CUSTID = MMColParam

(Where MMColParam = Request.QueryString=("CUSTID") )

You probably need this
SELECT product_id, SUM((((100 - RM) * SELLINGPRICE) * TONNESPERANNUM) / 100)
AS ANNUALCONTRIBUTION
FROM product
WHERE CUSTID = MMColParam
GROUP BY product_id

This will aggregate over the product for the customer, rahter than all
products for the customer.

Just a guess. Maybe you could post more SQL and description of the tables if
not.