#1
  1. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    May 2005
    Posts
    343
    Rep Power
    40

    Get Total for each Group in the loop


    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.
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,585
    Rep Power
    278
    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
    Code:
    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
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    May 2005
    Posts
    343
    Rep Power
    40
    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
    Last edited by Khrysller; January 22nd, 2013 at 12:48 PM.
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,585
    Rep Power
    278
    just add totals like

    Code:
    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
    
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    May 2005
    Posts
    343
    Rep Power
    40
    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.

    Code:
    <%
    	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>
  10. #6
  11. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,585
    Rep Power
    278
    try that
    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><% 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>
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    May 2005
    Posts
    343
    Rep Power
    40
    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:

    Code:
                <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.
  14. #8
  15. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,585
    Rep Power
    278
    that should print after the loop did you copied entire code?
    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>
  16. #9
  17. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    May 2005
    Posts
    343
    Rep Power
    40
    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...
  18. #10
  19. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,585
    Rep Power
    278
    it is give rep on the bottom now

    Comments on this post

    • Khrysller agrees : gave me great help

Similar Threads

  1. Total oil group reports record French profits (AFP)
    By RSS_News_User in forum Science News
    Replies: 0
    Last Post: February 14th, 2009, 07:03 AM
  2. Total oil group reports record French profits (AFP)
    By RSS_News_User in forum Science News
    Replies: 0
    Last Post: February 12th, 2009, 06:04 AM
  3. Total oil group reports record French profits (AFP)
    By RSS_News_User in forum Science News
    Replies: 0
    Last Post: February 12th, 2009, 03:05 AM
  4. Loop group by and give heading
    By moracom in forum ASP Development
    Replies: 1
    Last Post: March 27th, 2008, 12:07 PM
  5. group total
    By rajanikrishna in forum Microsoft Access Help
    Replies: 1
    Last Post: April 9th, 2004, 01:00 PM

IMN logo majestic logo threadwatch logo seochat tools logo