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?
 
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>

0
 
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
0
 
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>

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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.
0
 
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>
0
 
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.
0
 
David WilliamsonIT DirectorAuthor Commented:
that's cool.  I was unaware of coalesce().  what does it do?
0
 
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)
0
 
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....
0
 
David WilliamsonIT DirectorAuthor Commented:
the split sounds good, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.