RE: Help with Q of Q

[Mike Townend]

if you are just after the SUM of the columns, you could try something like


<CFOUTPUT>
    #ArraySum(ListToArray(ValueList(MasterQuery.Orig_amt)))#
</CFOUTPUT>


HTH

-----Original Message-----
From: Mickael [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 3, 2003 13:33
To: CF-Talk
Subject: Help with Q of Q

Hello All,

I am trying to run a query of a query and I am running into a little of SQL
trouble.  I keep getting this error Query Of Queries runtime error.
The aggregate function [SUM(_expression_)] cannot operate on an operand of
type [VARCHAR].

Here is the code I am trying to run

<cfquery name="MasterQuery" datasource="#appdsn#">
Select client_debt.debt_id
         , Orig_amt
         , Cur_Bal
         , Sum(Pmt_amt) as SUMPmtamt
         , Count(*) as TotalPayments
      from Client_debt
    Left outer
      join Client_pmt
        on client_debt.debt_id = client_pmt.debt_id
    group
        by client_debt.debt_id
         , Orig_amt
         , Cur_Bal

   
</cfquery>

<!--- <cfdump var="#masterquery#"> --->
<cfquery name="qry_GetTrackingReport" dbtype="query">

select count(*) as TotalAccounts
, Sum(Orig_amt) as SumOrigAmt
, Sum(Cur_Bal) as SumCurBal
, Sum(SUMPmtamt) as SUMSUMPmtAmt
, Sum(TotalPayments) as SUMTotalPayments
>From MasterQuery

</cfquery>
<cfdump var="#qry_GetTrackingReport#">

It look ok to me, I am not sure what I am not seeing, can someone point me
in the right direction.

Thanks

Mike
  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---