I have the following sql query:

SELECT Distinct productID, policyName, policyDur, policyDest, totalPrice
FROM VIAordered INNER JOIN VIAcust
ON VIAordered.orderID = VIAcust.orderID

Coupled in an html page with a recordset drawn from this Stored Procedure called report, I hav eencapsulated the values returned in the following code:

<% While (Not report.EOF) %>
<br>Policy ID:<%= report.Fields.Item("productID".Value %>

<br>Price Per Policy:<%= report.Fields.Item("totalPrice".Value %>

<br>Policy Name:<%= report.Fields.Item("policyName".Value %>

<br>Destination: <%= report.Fields.Item("policyDest".Value %>

<br>Duration:<%= report.Fields.Item("policyDur".Value %>

<% report.MoveNext
Wend
%>

What I have is a listing of all the distinct policies sold for a certain time in the month. I am trying to generate a report that tells me this, as well as counts the amount OF EACH sold. The logical step is to multiply the count by the policy price, and voila, you have a total amount for the month for that policy. What I cant figure out is how to get this total of each distinct policy ID. I can build 39 stored procedures for 39 products, and then pull them all into a two thousand line page, then use the recordcount, and multiply, but this makes my page very slow, and is tedious. What is the easy way of going about this? I have tried tons of possibilities, and I can think of no more.

I assume that the column totalPrice is the price for that policy?

you should look into using the sum() function, and also the count() function to report the values you're looking for.

I'll try to edit this SQL to do what you want, but you may need to mess with it more... the forum syntax checker seems to be on the fritz <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>
<pre id=code><font face=courier size=2 id=code>
SELECT productID, <b>count(productID) as total</b>, policyName, policyDur, policyDest, totalPrice, <b>sum(totalPrice) as total</b>
FROM VIAordered INNER JOIN VIAcust
ON VIAordered.orderID = VIAcust.orderID
<b>GROUP BY productID, policyName, policyDur, policyDest, totalPrice</b>
</font id=code></pre id=code>
hope that helps,

Joel Martinez

----------
Is this thing on?....