sum up with in a grouping

[woofer]

I have a query whose results are grouped in the cfoutput <cfoutput group="blah"> For each grouped section I want to sum (add up) a column, and have totals. How do I do this? I tried a query on a query

<cfquery name="getamt" dbtype="query">select sum(#trans_amount#) as sumtransamount from Get_Bill_History where 1=1</cfquery>
Total: <b>#getamt.sumtransamount#</b>

within the output & had this error: unknown exception condition

PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag

and when I try to put sum in the original query I get the error:
ORA-00937: not a single-group group function

SQL = "select CRM_CONTACTS."E_MAIL", sum(arv_trans_history.trans_amount) as sumtrans, crm_billing_history.service_code, arv_trans_history.salesman, arv_trans_history.applyto_key, arv_trans_history.history_key from crm_clients, crm_contacts, crm_billing_history, arv_trans_history where crm_clients.client_key=crm_contacts.client_key AND crm_contacts.contact_key = crm_billing_history.contact_key AND crm_billing_history.arv_key = arv_trans_history.history_key AND arv_trans_history.applyto_key not in (select applyto_key from arv_trans_history where 0=0 AND (applyto_key = 57492 and trans_type='INV' and trans_amount=3200)) AND to_char(crm_billing_history.trans_date, 'YYYY-MM-DD') BETWEEN '2001-08-05' AND '2002-08-05' AND crm_billing_history.trans_amount > 0 AND crm_billing_history.client_key = 15258 order by arv_trans_history.trans_date desc"

What is the correct way to do this?
If anyone can help me I would really appreciate it!!! I am stuck :(

Thanks,
Wolfie

[motobass]

The hack way would be to add it up as you output it. Reset it to zero for each new group and figure a way to output the summary column when that group is done. This would happen outside the inner cfoutput.

<cfoutput group="blah">

<cfset myRunningTotal = 0>

<cfoutput>

<cfset myRunningTotal = myRunningTotal + myColumn>

</cfoutput>

</cfoutput>

Another possibility, depending on your database, is to use an inner Select:

SELECT CRM_CONTACTS."E_MAIL",
SELECT (sum(arv_trans_history.trans_amount)
FROM myTable WHERE ... GROUP BY ...) AS subTotal,

...
arv_trans_history.history_key
FROM crm_clients ...

Dennis Spaag
Applications Developer
Gorilla Polymedia, Inc.
www.gorillapolymedia.com