Get Total for each Group in the loop

[Khrysller]

Hi : )

I have this question here in stackoverflow:

SumTotal in the end of each group classic asp - Stack Overflow

I am trying to output the total by Groups... if I have 1 group it works ok, but with 2 or more, i dont know how to break the code and insert the total...
can someone help me?

Thanks a lot.

[gk53]

How you read data from database? Do you run something like
sql="select * from group_number, TitleUm"
id so and you have recordset for example rs

group=""
TitleUm=""
do while not rs.eof
if group<>rs("group_name") then
group=rs("group_name")
response.write "(" & rs("group_number") & ")" & "   " & rs("group_name")
TitleUm=rs("TitleUm")
end if
if TitleUm<>rs("TitleUm") then
TitleUm=rs("TitleUm")
response.write rs("TitleUm") & "  " & TitleTres  & "  " & TitleQuatro
TitleTres = 0
TitleQuatro  = 0
end if
TitleTres = TitleTres + cint(rs("TitleTres"))
TitleQuatro = TitleQuatro + cint(rs("TitleQuatro"))
rs.moveNext
loop

[Khrysller]

Thanks for the reply GK.
Yes, I do a select * from table where....
Everything is working.... besides the bottom line with the total for each group.

I am trying to do this for some time... the stackoverflow example has some table build in... i got almost everything done, looking for to put a footer line after the last item from a group.
I am making the calculations already...
so when I have only 1 group, i get it working. When I have like 15 groups, my footer line only show in the last group but it calculates for the 15 groups.

I couldnt make the above example to work

My big problem is how to find the last item from the group in the loop and than put a footer line so i can calculate the totals?

I need to place the code:

<tr class="Total">
<td>TOTAL</td>
<td><% = Total_Tres %></td>
<td><% = Total_Quatro %></td>
</tr>

...somewhere for the loop get it and put bellow the last item in the group

thanks for any more info

[gk53]

just add totals like

TitleTresTotal= 0 
TitleQuatroTotal = 0
group=""
TitleUm=""
do while not rs.eof
if group<>rs("group_name") then
group=rs("group_name")
response.write "(" & rs("group_number") & ")" & "   " & rs("group_name")
TitleUm=rs("TitleUm")
end if
if TitleUm<>rs("TitleUm") then
TitleUm=rs("TitleUm")
response.write rs("TitleUm") & "  " & TitleTres  & "  " & TitleQuatro
TitleTresTotal = TitleTresTotal + TitleTres
TitleQuatroTotal =  TitleQuatroTotal + TitleQuatro
TitleTres = 0
TitleQuatro  = 0
end if
TitleTres = TitleTres + cint(rs("TitleTres"))
TitleQuatro = TitleQuatro + cint(rs("TitleQuatro"))
rs.moveNext
loop
TitleTresTotal = TitleTresTotal + TitleTres
TitleQuatroTotal =  TitleQuatroTotal + TitleQuatro


response.write TitleTresTotal & "    " &  TitleQuatroTotal

[Khrysller]

GK, hi again, still can get this to work... this is what i have:
But this will print the Total rows only in the last loop group...
I am missing something stupid here... please can you show me?

thanks again.

<%
	Set MyOrders= TarefasConexaoMSSQL.Execute("SELECT * FROM TABLEABC")
%>
	<table>
		<%
			currentGroupName = ""
			previousGroupName = ""

			Do Until MyOrders.EOF
			currentGroupName = MyOrders("Group")

			If currentGroupName <> previousGroupName Then
		%>
		<tr class="Group">
			<td colspan="5"><% Response.Write currentGroupName %></td>
		</tr>
		<tr class="Header">			
			<th>Description</th>
			<th>Owner</th>
			<th>Quantity</th>
			<th>ValueOne</th>
			<th>ValueTwo</th>
		</tr>
		<%
			End If
		%>
		<%
			Description= MyOrders("Description")
			Owner= MyOrders("Owner")
			Quantity= CInt(MyOrders("Quantity"))
			ValueOne= CCur(MyOrders("ValueOne"))
			ValueTwo= CCur(MyOrders("ValueTwo"))
			TotalQuantity= FormatNumber(TotalQuantity + Quantity,2)
			TotalValueOne= FormatNumber(TotalValueOne + ValueOne,2)
			TotalValueTwo= FormatNumber(TotalValueTwo + ValueTwo,2)
		%>

		<tr class="Itens">
			<td><% Response.Write Description %></td>
			<td><% Response.Write Owner %></td>
			<td><% Response.Write Quantity %></td>
			<td><% Response.Write ValueOne %></td>
			<td><% Response.Write ValueTwo %></td>
		</tr>
		<%
			previousGroupName = currentGroupName
			MyOrders.MoveNext
			Loop
			MyOrders.Close
		%>
		<tr class="Total">
			<td>[ Description doesn't need to have Total]</td>
			<td>[ Owner doesn't need to have Total]</td>
			<td><% Response.Write TotalQuantity %></td>
			<td><% Response.Write TotalValueOne %></td>
			<td><% Response.Write TotalValueTwo %></td>
		</tr>
	</table>

[gk53]

try that

<%
	Set MyOrders= TarefasConexaoMSSQL.Execute("SELECT * FROM TABLEABC")
%>
	<table>
		<%
			currentGroupName = ""
			Do while not MyOrders.EOF
			If currentGroupName <> MyOrders("Group") Then
            if currentGroupName <> "" then
            %>
            <tr class="Total">
			    <td>[ Description doesn't need to have Total]</td>
			    <td>[ Owner doesn't need to have Total]</td>
			    <td><% Response.Write FormatNumber(TotalQuantity,2) %></td>
			    <td><% Response.Write FormatNumber(TotalValueOne,2) %></td>
    			<td><% Response.Write FormatNumber(TotalValueTwo,2) %></td>
    		</tr>
            <%
            end if
            currentGroupName = MyOrders("Group")
            TotalQuantity = 0
			TotalValueOne = 0
			TotalValueTwo = 0
		%>
		<tr class="Group">
			<td colspan="5"><% Response.Write currentGroupName %></td>
		</tr>
		<tr class="Header">			
			<th>Description</th>
			<th>Owner</th>
			<th>Quantity</th>
			<th>ValueOne</th>
			<th>ValueTwo</th>
		</tr>
		<%
			End If
		%>
		<tr class="Itens">
			<td><% Response.Write MyOrders("Description") %></td>
			<td><% Response.Write MyOrders("Owner") %></td>
			<td><% Response.Write MyOrders("Quantity") %></td>
			<td><% Response.Write ccur(MyOrders("ValueOne")) %></td>
			<td><% Response.Write ccur(MyOrders("ValueTwo")) %></td>
		</tr>
		<%
			TotalQuantity = TotalQuantity + MyOrders("Quantity")
			TotalValueOne = TotalValueOne + MyOrders("ValueOne")
			TotalValueTwo = TotalValueTwo + MyOrders("ValueTwo")
            GrandTotalQuantity = GrandTotalQuantity + MyOrders("Quantity")
            GrandTotalValueOne = GrandTotalValueOne + MyOrders("ValueOne")
            GrandTotalValueTwo = GrandTotalValueTwo + MyOrders("ValueTwo")
			'TotalQuantity= FormatNumber(TotalQuantity + CInt(MyOrders("Quantity")),2)
			'TotalValueOne= FormatNumber(TotalValueOne + CCur(MyOrders("ValueOne")),2)
			'TotalValueTwo= FormatNumber(TotalValueTwo + CCur(MyOrders("ValueTwo")),2)
			
            MyOrders.MoveNext
			Loop
			MyOrders.Close
		%>
		<tr class="Total">
			<td>[ Description doesn't need to have Total]</td>
			<td>[ Owner doesn't need to have Total]</td>
			<td><% Response.Write FormatNumber(TotalQuantity,2) %></td>
			<td><% Response.Write FormatNumber(TotalValueOne,2) %></td>
			<td><% Response.Write FormatNumber(TotalValueTwo,2) %></td>
		</tr>
		<tr class="Total">
			<td>Grand Totals</td>
			<td></td>
			<td><% Response.Write FormatNumber(GrandTotalQuantity,2) %></td>
			<td><% Response.Write FormatNumber(GrandTotalValueOne,2) %></td>
			<td><% Response.Write FormatNumber(GrandTotalValueTwo,2) %></td>
		</tr>
	</table>

[Khrysller]

Amazing GK, really thanks for the help... you went even futher and did the gran total... thanks...

but for some reason, the last group of the list (the last loop) isn't showing the Total block, this:

            <tr class="Total">
			    <td>[ Description doesn't need to have Total]</td>
			    <td>[ Owner doesn't need to have Total]</td>
			    <td><% Response.Write FormatNumber(TotalQuantity,2) %></td>
			    <td><% Response.Write FormatNumber(TotalValueOne,2) %></td>
    			<td><% Response.Write FormatNumber(TotalValueTwo,2) %></td>
    		</tr>

All others groups are showing nicely now
can you light me again? thanks.

[gk53]

that should print after the loop did you copied entire code?

<%
	Set MyOrders= TarefasConexaoMSSQL.Execute("SELECT * FROM TABLEABC")
%>
	<table>
		<%
			currentGroupName = ""
			Do while not MyOrders.EOF
			    If currentGroupName <> MyOrders("Group") Then
                    if currentGroupName <> "" then
                    %>
                    <tr class="Total">
			            <td>[ Description doesn't need to have Total]</td>
			            <td>[ Owner doesn't need to have Total]</td>
			            <td><% =FormatNumber(TotalQuantity,2) %></td>
			            <td><% =FormatNumber(TotalValueOne,2) %></td>
    			        <td><% =FormatNumber(TotalValueTwo,2) %></td>
    		        </tr>
                    <%
                    end if
                    currentGroupName = MyOrders("Group")
                    TotalQuantity = 0
			        TotalValueOne = 0
			        TotalValueTwo = 0
		        %>
		        <tr class="Group">
			        <td colspan="5"><% Response.Write currentGroupName %></td>
		        </tr>
		        <tr class="Header">			
			        <th>Description</th>
			        <th>Owner</th>
			        <th>Quantity</th>
			        <th>ValueOne</th>
			        <th>ValueTwo</th>
		        </tr>
		    <%
			    End If
		    %>
		    <tr class="Itens">
			    <td><% =MyOrders("Description") %></td>
			    <td><% =MyOrders("Owner") %></td>
			    <td><% =MyOrders("Quantity") %></td>
			    <td><% =ccur(MyOrders("ValueOne")) %></td>
			    <td><% =ccur(MyOrders("ValueTwo")) %></td>
		    </tr>
		    <%
			    TotalQuantity = TotalQuantity + MyOrders("Quantity")
			    TotalValueOne = TotalValueOne + MyOrders("ValueOne")
			    TotalValueTwo = TotalValueTwo + MyOrders("ValueTwo")
                GrandTotalQuantity = GrandTotalQuantity + MyOrders("Quantity")
                GrandTotalValueOne = GrandTotalValueOne + MyOrders("ValueOne")
                GrandTotalValueTwo = GrandTotalValueTwo + MyOrders("ValueTwo")
			
                MyOrders.MoveNext
			Loop
			MyOrders.Close
		%>
		<tr class="Total">
		    <td>[ Description doesn't need to have Total]</td>
		    <td>[ Owner doesn't need to have Total]</td>
		    <td><% =FormatNumber(TotalQuantity,2) %></td>
		    <td><% =FormatNumber(TotalValueOne,2) %></td>
    	    <td><% =FormatNumber(TotalValueTwo,2) %></td>
        </tr>
		<tr class="Total">
			<td>Grand Totals</td>
			<td></td>
			<td><% =FormatNumber(GrandTotalQuantity,2) %></td>
			<td><% =FormatNumber(GrandTotalValueOne,2) %></td>
			<td><% =FormatNumber(GrandTotalValueTwo,2) %></td>
		</tr>
	</table>

[Khrysller]

you are right..
everything is flawless now... thanks A LOT GK.
I would like to click in the scale button but it seens to b no more with this theme...

[gk53]

it is give rep on the bottom now