Thread: Summing for each group only

Hi All,
I need help trying to get results like below, the part in red.

I can calculate the:
qtr qtys to get qty total
and
qty total x cost to get total cost
my issue is with adding only for each Group (see text in red).

right now i can sum all to get grand total, but need help with just making it add for group1,
then group 2, etc. is this normally handled in the sql query or in the
asp rs?

any help/suggestions is greatly appreciated.


----------------1Q - 2Q - 3 Q - 4Q - Qty Total- Cost - Total Cost
Group1
-subGroup1
-----------Item1 1 - 1 - 0 - 0 - 2 - $100 - $200
-----------Item2 0 - 1 - 0 - 1 - 2 - $100 - $200
-subGroup2
-----------Item3 1 - 0 - 0 - 1 - 2 - $100 - $200
-----------Item4 0 - 0 - 0 - 1 - 1 - $100 - $100

Group2
-subGroup3
-----------Item5 0 - 0 - 0 - 1 - 1 - $100 - $100
-----------Item6 2 - 2 - 2 - 1 - 7 - $100 - $700

ETC....

Group1Totals 2 - 2 - 0 - 3 - 7 - blank -$700
Group2Totals 2 - 2 - 2 - 1 - 7 - blank -$800
ETC....
GRAND Total 4 - 4 - 2 - 4 - 14 - blank -$1500

Ok, I've taken out the code for my grand total, I'm trying to just get the totals for each Category.

I went back and grouped my sql view and create SumItem for ItemCost.

Here is what I have so far but I'm having some trouble. 2 Problems - problem one is I'm not getting the right CategoryDesc to match the right Sum (code in red). problem two, it's not really where I want to have it but I've moved it and moved it and not getting the results I want. I would like this row to really be at the bottom (like example in first post). Please ignore the blank rows in code below, I haven't yet added those.

Code:

<%
Dim strLastCategory, strLastGrp, strCost,strSumItem
%>

    <TABLE border="3" width=1000>
<col width=100><col width=10><col width=10><col width=10><col width=10><col width=10><col width=20><col width=20><col width=20><col width=20><col width=20><col width=20>
      <TR bgcolor="#6699CC">
<TH>Description</TH>
<TH>1Q</TH>
<TH>2Q</TH>
<TH>3Q</TH>
<TH>4Q</TH>
<TH>Total Qty</TH>
<TH>Cost</TH>
<TH>1Q Sum</TH>
<TH>2Q Sum</TH>
<TH>3Q Sum</TH>
<TH>4Q Sum</TH>
<TH>Year Total</TH>
      </TR>
<%
strLastCategory = ""
strLastGrp = ""
strCost = ""


	do while not rs.EOF
	if i > iStopRec then
		exit do
	end if

	strCurrentCategory = rs("CategoryDesc")
	strCurrentGrp = rs("GrpDesc")
	
	
	If strCurrentCategory<>strLastCategory Then
	Response.Write("<tr bgcolor=#D3D3D3><td colwidth=""100"" colspan=""12""><b>" & strCurrentCategory & "</b></td></tr>")
	Response.Write("<tr bgcolor=yellow><td colwidth=""100""><b>" & strCurrentCategory & "</b></td>")
	Response.Write("<td colwidth=""100"">&nbsp;</td>")
	Response.Write("<td colwidth=""100"">&nbsp;</td>")
	Response.Write("<td colwidth=""100"">&nbsp;</td>")
	Response.Write("<td colwidth=""100"">&nbsp;</td>")
	Response.Write("<td colwidth=""100"">&nbsp;</td>")
	Response.Write("<td colwidth=""100""><b>" & strSumItem & "</b></td></tr>")
	strSumItem = 0	
             End If
	strLastCategory = strCurrentCategory
	strSumItem = strSumItem + rs("SumItem")

	If strCurrentGrp<>strLastGrp Then	
	Response.Write("<tr bgcolor=#D3D3D3><td colwidth=""100"" colspan=""12""><i>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & strCurrentGrp & "</i></td></tr>")	
	End If
	strLastGrp = strCurrentGrp
	
	i = i + 1
	
%>
<%
intItemCost = rs("ItemCost")
intItem = FormatCurrency((intItemCost / 1000),0)
%>

<TR>
<TD align=right><%=rs("ItemDesc")%> &nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#E0FFFF" align=center><%= intItem %></TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
<TD bgcolor="#F4A460" align=center>&nbsp;</TD>
 </TR>	

<%	
rs.MoveNext
loop
%>

<%
rs.Close
%>

</table>

Thanks for any suggestions with this.

ok, i hope this shows up ok.

Description---1Q--2Q--3Q--4Q--TotalQty--Cost--1QSum--2QSum--3QSum--4QSum--YrTotal

CategoryName1
CategoryName1---------------------------Cost Is Blank (should show $36)
--GroupName1
----Item1--------------------------------$10----------------------------
----Item2--------------------------------$11----------------------------
--GroupName2
----Item3--------------------------------$5-----------------------------
----Item4--------------------------------$2-----------------------------
----Item5--------------------------------$8-----------------------------
CategoryName2
CategoryName2---------------------------$36 (this total is correct 4 Category1, when it should show Category2 total $15)
--GroupName3
----Item6---------------------------------$7-----------------------------
----Item7---------------------------------$5-----------------------------
--GroupName4
----Item8---------------------------------$3-----------------------------
CategoryName3
CategoryName3---------------------------$15 (this total is correct 4 Category2)

Right now, I have all the qtr data blank, concentrating on the Cost field here.

Originally Posted by mehere

in order to get the correct sum ... in the correct category you need to display it at the end. since you are looping through the records to get your total. until you get to the records, you don't have a total.

Thanks Mehere, but when I've tried putting the code in red at the end, I only get one big grand total. Do I have to perform another loop after the one I have?

Originally Posted by mehere

okay ... try changing this line

Code:

If strCurrentCategory<>strLastCategory Then

to this line

Code:

If strCurrentCategory<>strLastCategory and strLastCategory <> "" Then

whoops...hehe...that gets rid of my first CategoryName1 all together and my CategoryName2 shows but with cost sum of Category1 items.

I'll keep moving it around and see where I can put it....when I tried to put at the end it would just give me the lastCategoryName with a total of everything...I need to to show each CategoryName and sum items in that Category.

Thanks...I'll keep trying but if you have any other ideas please let me know.