?
Solved

Q of Q: sum(varchar)?

Posted on 2006-04-11
12
Medium Priority
?
960 Views
Last Modified: 2013-12-24
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?

0
Comment
Question by:David Williamson
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 16432447
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
 
LVL 2

Author Comment

by:David Williamson
ID: 16432514
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
 
LVL 18

Expert Comment

by:Plucka
ID: 16432545
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 7

Expert Comment

by:JeffHowden
ID: 16433792
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
 
LVL 2

Author Comment

by:David Williamson
ID: 16437133
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
 
LVL 7

Accepted Solution

by:
JeffHowden earned 672 total points
ID: 16437859
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
 
LVL 2

Author Comment

by:David Williamson
ID: 16438592
that's cool.  I was unaware of coalesce().  what does it do?
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 664 total points
ID: 16438892
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
 
LVL 16

Assisted Solution

by:RCorfman
RCorfman earned 664 total points
ID: 16462185
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
 
LVL 2

Author Comment

by:David Williamson
ID: 16653300
the split sounds good, thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question