Greetings,
I can't seem to get a hold of this one.
I am trying to use sum and advanced grouping
What I have:
Query:fp_sQry="SELECT AR1_CustomerMaster.CustomerName, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SODescription, Sum(ARO_InvHistoryDetail.SOUnitPrice) AS SumOfSOUnitPrice FROM (ARN_InvHistoryHeader INNER JOIN ARO_InvHistoryDetail ON ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber) INNER JOIN AR1_CustomerMaster ON ARN_InvHistoryHeader.CustomerNumber = AR1_CustomerMaster.CustomerNumber GROUP BY AR1_CustomerMaster.CustomerName, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SODescription HAVING (((ARO_InvHistoryDetail.SOItemNumber) Is Not Null)) ORDER BY AR1_CustomerMaster.CustomerName;"
And
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%
DIM customer,tempcustomer
customer=FP_FieldVal(fp_rs,"CustomerName")
If customer <> tempcustomer then
tempcustomer=customer
%>
<td bgcolor="#FFFFCC" colspan=2><%=tempcustomer%></td>
</tr>
<%end if%>
<tr>
<td bgcolor="#FFFFFF" >
<%=FP_FieldVal(fp_rs,"SODescription")%> <%=formatcurrency(FP_FieldVal(fp_rs,"SumofSOUnitPrice"),2)%></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
This works great and returns what I expect:
CustomerName1
Grouped Product Description1 - Grouped Product Price
Grouped Product Description2 - Grouped Product Price
CustomerName2
Grouped Product Description1 - Grouped Product Price
Grouped Product Description2 - Grouped Product Price
etc....
What I need is:
CustomerName1 - SUM(Grouped Product Price)
Grouped Product Description1 - Grouped Product Price
Grouped Product Description2 - Grouped Product Price
CustomerName2 - SUM(Grouped Product Price)
Grouped Product Description1 - Grouped Product Price
Grouped Product Description2 - Grouped Product Price
etc....
Any guidance will be greatly appreciated,
Thanks,
gd
