• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Return Column Totals

New Here ,
Jan 24, 2008 Jan 24, 2008

Copy link to clipboard

Copied

I am using cfstoredproc to pull back some results into a html table. All this is working fine (including calculating the row totals in my actual sql query), until I come to total the COLUMNS up that are returned. If it is possible, I want to do this with ColdFusion, not SQL. Surely there's a way? (All returned values are ints).

I'm using the following (where "getTotals" is cfprocresult name of my cfstoredproc) to generate :

<tr>
<td>Column Heading 1</td>
<td>Column Heading 2</td>
<td>Column Heading 3</td>
<td>Column Heading 5</td>
<td>Column Heading 6</td>
<td>TOTAL Heading</td>
</tr>
<cfoutput query="getTotals">
<tr>
<td>#int1#</td>
<td>#int2#</td>
<td>#int3#</td>
<td>#int4#</td>
<td>#int5#</td>
<td>#TOTAL#</td>
</tr>
</cfoutput>

I need to add a third <tr></tr> to now calculate the column totals.

I've tried inserting the values returned in int1, int2, etc for each column into an array to calculate a total from that, but it is only adding the value returned last?? And I cannot change the first part to <cfloop query="getTotals"> as it doesn't recognise #int1#, etc. It just gets printed to the screen as #int1#.

Please let me know if I haven't explained well enough.

Thanks in advance for the help.
TOPICS
Advanced techniques

Views

1.8K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 24, 2008 Jan 24, 2008

Copy link to clipboard

Copied

I got this working with SQL, but would still be interested in a possible CF workaround if it's out there, thanks 🙂

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jan 24, 2008 Jan 24, 2008

Copy link to clipboard

Copied

One option is run a QoQ to get the SUM() of the columns. Though QoQ can be quirky at times with things like null values, etcetera. You could also use variables to keep a running total as you loop through the query.

<cfquery name="getGrandTotals" dbtype="query">
SELECT

SUM(CAST(int1 AS integer)) AS int1,
SUM(CAST(int2 AS integer)) AS int2,
SUM(CAST(int3 AS integer)) AS int3,
SUM(CAST(int4 AS integer)) AS int4,
SUM(CAST(int5 AS integer)) AS int5,
SUM(CAST(Total AS integer)) AS Total
FROM getTotals
</cfquery>

Though I would probably do it with sql.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

Rubo18 wrote
I got this working with SQL, but would still be interested in a possible CF workaround

Suppose the name of the (numeric) column you wish to sum is myCol. Then this should do in CFML

<cfset sumCol = 0>
<cfloop query="getTotals">
<cfset sumCol = sumCol + myCol[getTotals.currentRow]>
</cfloop>
column sum: <cfoutput>#sumCol#</cfoutput>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

quote:

Originally posted by: rubo18
I got this working with SQL, but would still be interested in a possible CF workaround if it's out there, thanks :)


As you can see you have a few choices 🙂 ArraySum is an equally valid option. Though you may need a few more functions to convert the query column to an array.

<cfset columnTotal = ArraySum(ListToArray(ValueList(yourQuery.ColumnName)))>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

quote:

Originally posted by: -==cfSearching==-
quote:

Originally posted by: rubo18
I got this working with SQL, but would still be interested in a possible CF workaround if it's out there, thanks :)


As you can see you have a few choices 🙂 ArraySum is an equally valid option. Though you may need a few more functions to convert the query column to an array.

<cfset columnTotal = ArraySum(ListToArray(ValueList(yourQuery.ColumnName)))>

Or you may not.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

Dan Bracuk,

I am curious. Why do you say that? I could not get arraysum(NameOfQuery["NameOfColumn"]); to work without the extra functions. That would be cool it if worked without them.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

quote:

Originally posted by: -==cfSearching==-
Dan Bracuk,

I am curious. Why do you say that? I could not get arraysum(NameOfQuery["NameOfColumn"]); to work without the extra functions. That would be cool it if worked without them.

What happened the last time you tried it?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

YourSum = arraysum(NameOfQuery["NameOfColumn"]);

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

An error. The error message suggests CF cannot implicitly convert a coldfusion.sql.QueryColumn object to an array. Does it work for you?

[Table (rows 3 columns ColumnName): [ColumnName: coldfusion.sql.QueryColumn@14f8035] ] is not indexable by NameOfColumn

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

quote:

Originally posted by: -==cfSearching==-
An error. The error message suggests CF cannot implicitly convert a coldfusion.sql.QueryColumn object to an array. Does it work for you?

[Table (rows 3 columns ColumnName): [ColumnName: coldfusion.sql.QueryColumn@14f8035] ] is not indexable by NameOfColumn

I copied working code and changed the variable name, query name and column name.

Was your syntax exactly the same as mine, including the double quotes?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jan 25, 2008 Jan 25, 2008

Copy link to clipboard

Copied

Yes. I had mistakenly swapped out one of the column names. That is why it did not work. It does now. Cool.

Interesting stuff. I looked under the hood and discovered ArraySum accepts a java.util.List and coldfusion.sql.QueryColumn implements java.util.List. That must be how it is able to work. Learn something new every day. Though I think I will stick with QoQ or variables lest they change QueryColumn the way they did coldfusion.runtime.Struct.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 26, 2008 Jan 26, 2008

Copy link to clipboard

Copied

Rubo18

There is one advantage of adding the column entries one by one to get the sum (as I did). It enables you to validate. For example, if the column myCol allows null you could do

<cfset colEntry = myCol[getTotals.currentRow]>
<cfif isNumeric(colEntry)>
<cfset sumCol = sumCol + colEntry>
</cfif>


edited: Rubo18 in place of -==cfSearching==-

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jan 31, 2008 Jan 31, 2008

Copy link to clipboard

Copied

BKBK,

Yes, I agree. Calculating totals within the loop is probably the most bullet proof solution. That is why I suggested it as an alternative. Though you could also use COALESCE to eliminate the nulls, or obtain the totals in sql. That is usually my first preference.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 01, 2008 Feb 01, 2008

Copy link to clipboard

Copied

LATEST
-==cfSearching==-

I meant to direct my previous post to Rubo18, not to you. I probably didn't scroll high enough when copying the name. My apologies.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 28, 2008 Jan 28, 2008

Copy link to clipboard

Copied

Thankyou everyone, I shall try some of these out! Although I found the SQL way was much easier than I anticipated it to be, so may just stick with that.

Umm, do I mark you all as giving the answer?? Sorry, don't know the proper etiquette here!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 28, 2008 Jan 28, 2008

Copy link to clipboard

Copied

quote:

Originally posted by: rubo18
Thankyou everyone, I shall try some of these out! Although I found the SQL way was much easier than I anticipated it to be, so may just stick with that.

Umm, do I mark you all as giving the answer?? Sorry, don't know the proper etiquette here!

Thanking us was more than enough.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation