Q of Q: sum(varchar)?

I start with this query:

<cfquery datasource="ttonline" name="PerTrade" cachedwithin="#createtimespan(0,0,0,5)#">
      select journalid,trxnid,ticker,
      -sum(shares * stockprice) - sum(commission) as gain_loss from journal inner join trxns using(journalid)
      where
      journal.journalid in (#theJIDList#)
      group by journal.journalid
</cfquery>

'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
</cfquery>

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?

LVL 2
David WilliamsonIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PluckaCommented:
theamzngq,

Functions such as SUM, MAX and many others, are not supported in query of query mode, only the base SQL command set is supported.

Regards
Plucka
David WilliamsonIT DirectorAuthor Commented:
Actually, sum IS supported in query of queries.  Under the 'Using Query of Queries' section of the ColdFusion MX7 online guide:

http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/js/html/wwhelp.htm?href=00001272.htm#1138862

Actually, when I was browsing to find that link for you, I stumbled on the answer to my question:

http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/js/html/wwhelp.htm?href=00001272.htm#1138862

I tried this, and it works:

<cfquery dbtype="query" name="test">
   select sum(cast(gain_loss as decimal)) from pertrade
</cfquery>

PluckaCommented:
Ohh,

Sorry for leading you down the garden path, I think I was thinking of joins which I don't believe are supported in Q of Q's.

Glad you sorted it though.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

JeffHowdenCommented:
If you're not using any conditions to narrow down the results of the Sum(), then why not just add all values in that column like so:

<cfscript>
  sum_gain_loss = ArraySum(ListToArray(ValueList(pertrade.gain_loss)));
</cfscript>
David WilliamsonIT DirectorAuthor Commented:
that would probably work as well, actually.  But in my case, I have a couple of different sums I'm going for besides the entire thing, so there are conditions attached to some of the values I'm trying to pull.
JeffHowdenCommented:
I had a thought that maybe some of  your records have a NULL value in the gain_loss column.  That could cause the CF QoQ error you're seeing.  Try eliminating those by using Coalesce() on the gain_loss column in your original query:

<cfquery datasource="ttonline" name="PerTrade" cachedwithin="#createtimespan(0,0,0,5)#">
     select journalid,trxnid,ticker,
     Coalesce(-sum(shares * stockprice) - sum(commission), 0) as gain_loss from journal inner join trxns using(journalid)
     where
     journal.journalid in (#theJIDList#)
     group by journal.journalid
</cfquery>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David WilliamsonIT DirectorAuthor Commented:
that's cool.  I was unaware of coalesce().  what does it do?
mrichmonCommented:
coalesce and IsNull are very similar and only supported in SQL and other higher databases (i.e. access support is spotty).

IsNUll returns the first value, unless it is null then the second

IsNull(value1, value2)

Coalesce returns the first non-null value - and can have more than one...

Coalesce(value1, value2, value3, value4, etc)
RCorfmanCommented:
You could also just sum the data while you are outputing it... this has the advantage of not going through the set of data multiple times, once to sum, then once to output, etc...

If you aren't outputting all the data, but just the sums, then you should sum it in the database....
David WilliamsonIT DirectorAuthor Commented:
the split sounds good, thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.