# Thread: ASP to calculate sum of records

1. ## ASP to calculate sum of records

Hi guys,
pmldb.mdb
There are four fields I'm concerned about,
parts, weight, material grade and size.

I need an asp script to calculate the sum of the 'weight' for each part.
Thus I need the total weight for each of the part called Pipes, consumables, valves and instruments.
and also i need the option where i can group it by either material grade, size or part.
An example, if there are 130 records; 70 of them are pipes, 30 are consumables, 20 are valves and 10 are instruments. I would need the total sum of the weight
of all 70 pipes. The same would apply to consumables, valves and instruments. And have them them all displayed with the total for each four.
ps. I've had a go at it below. Please, if you've got a better idea, then it would be appreciated if you can show me, the below one is incomplete (but would work). My asp is a bit rusty, just started learning it.

<%
'Connect to database

'Query database
SQL = "SELECT * FROM Table WHERE Part = 'Pipes'"
Set DB.Execute(SQL)

intQuant = 0

Do Until RS.EOF
intQuant = intQuant + RS("Weight")
RS.MoveNext
Loop
%>

2. ## Re: ASP to calculate sum of records

What I've been doing is a three-step process:
1. Use a Make Table Query to create a table with the appropriate fields
2. Query the table using the SQL aggregate functions (Sum, Avg, etc)
3. After all the data are presented, I run a Drop Table query.

3. ## Re: ASP to calculate sum of records

Hi Mark (Nice name [img]/forums/images/smilies/smile.gif[/img]
I see what your saying. Ok, what do you think of this one that I did below?.
Also, Is there a way I can display the results of the records for pipes for example?. So the 20 records that were involved for the calculation of the weight of the pipes will be displayed.? Thanks in advance

<%
'Connect to database
db.Open "DSN=ImportExcel"
'Query database
set RS = db.execute("SELECT parts,sum(weight) as SWEIGHT FROM tblSheets GROUP BY Parts")
%>
<table>
<%
Do Until RS.EOF
%>
<tr>
<td>
<%=RS("Parts")%>
</td>
<td>
<%=RS("SWeight")%>
</td>
</tr>
<%
RS.MoveNext
Loop
%>
</table>

4. ## Re: ASP to calculate sum of records

Hi Mark,

Your code looks great! That should do the trick...

As for showing each individual record for Pipes, you'll need to use a different Select statement for this. If you need to see each individual record for all Parts, your original method might be best. The Group By is only appropriate if you only want to see a summary.

Hope this helps!

5. ## Re: ASP to calculate sum of records

Hey MarkJ ~ I tried the "more direct" method, using ID, Year, and Month as Select fields (because there are criteria on them), with SumOF the other fields, and i always wind up with distinct records for each year and month. I'll go back and look at my SQL again, perhaps I can simplify what I'm doing...

6. ## Re: ASP to calculate sum of records

<P ID="edit" class=small>(Edited by MarkJ on 21-May-03 21:30. Corrected Group By clause (removed Sum(Weight) - not needed in Group By if it's an Aggregate function))</P>Hi Mark,

My suggestion is along the same lines as Peter's, except it may be a little more direct.

You can use a SQL statement to do all of this work for you. You can use the GroupBy clause of SQL kinda like this:
(assumes that your field names are Part and Weight)

SELECT Part, SUM(Weight) AS SumOfWeight FROM Table GROUP BY Weight

This should give you a distinct group for each part along with the sum of the weights for each part. This should save having to loop through a recordset (which will also work, but this way should be quicker). If you need more fields, be sure to add them to the SELECT clause as well as the Group By clause.

Hope this helps!

7. ## Re: ASP to calculate sum of records

Hi Peter,

That's the nature of the Group By clause - the output will return a distinct record made up of each field in the clause. It makes sense that if you want to see, for instance, sums for certain fields along with their ID, month, and year that you'll see distinct records with all of the specified fields. Now, if you want to see sums for each ID, leave off month and year to group by only ID.

I hope this makes sense. My understanding of SQL is decent, but I haven't gotten to the point of being able to adequately explain it to others yet...

For more information you can check one of my favorite SQL sites: SQL Team

8. ## Re: ASP to calculate sum of records

AHA! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Changing the "Group By" to "Expression" in my In Access "query builder" saves 2 steps in my 3-step process!

Thanks Marks - you both taught me something! I can throw even MORE code away <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•