Q of Q: sum(varchar)?
Posted on 2006-04-11
I start with this query:
<cfquery datasource="ttonline" name="PerTrade" cachedwithin="#createtimespan(0,0,0,5)#">
-sum(shares * stockprice) - sum(commission) as gain_loss from journal inner join trxns using(journalid)
journal.journalid in (#theJIDList#)
group by journal.journalid
'gain_loss' contains a number. when I run the following query of queries on it:
<cfquery dbtype="query" name="GainLoss">
select sum(gain_loss) as total from pertrade
I get the error:
The aggregate function [SUM(expression)] cannot operate on an operand of type [VARCHAR].
Its seems pretty clear that the 'gain_loss' column has a number stored as text, which is why the Q of Q is complaining. Is there a toNumber type function that I can run somewhere to make sure that 'gain_loss' is in fact a number?