Inspiring

Question

ArraySum

Forum|Forum|14 years ago
September 16, 2011
2 replies
1480 views

Hi,

with the code below, i got the result displayed in the table below. I want the total for all, but my code gave me the wrong total 10 instead of 15. Can any of you tell me why?

Thanks

<cfloop query="getNames">
<cfquery name="countCode" datasource="#request.dsn#">
Select count(i.Identifier) as totalRecs
FROM name
WHERE name_code = '#getNames.name_code#'
</cfquery>
<tr>
<td>#getNames.name#</td>
<td>#countCode.totalRecs#</td>

<cfset NumberArray = ArrayNew(1)>
<cfset Temp = Arrayprepend(NumberArray,countCode.totalRecs)>
</tr>
</cfloop>
<tr>
<td>Total All</td>
<td>#ArraySum(NumberArray)#</td>
</tr>

Name	Count
Gas	0
Tanks	0
Radiations	5
Medical	0
Personal	0
Fax	10
Total	10

Getting started

This topic has been closed for replies.

A

Adam Cameron.

Inspiring

You are recreating your array every iteration of the loop. So your array will only ever have one element (ultimately the last one).

You should have been able to work this out for yourself by inspecting the values of your variables (which should always be part of troubleshooting ths sort of issue).

--

Adam

K

kt03Author

Inspiring

so beside the array, what is the other way to get the total for all rows?

thanks

ilssac

Inspiring

kt03 wrote:
so beside the array, what is the other way to get the total for all rows?

<cfquery name="demo"...>
SELECT
     category,
     count(category) as count
FROM 
     atable
WHERE
     something = 'optionalFilterValue'
</cfquery>
<cfoutput query="demo">
#demo.category# = #demo.count#
</cfoutput>
<cfoutput>#arraySum(demo["count"])#</cfoutput>

D

Dan_Bracuk

Inspiring

Your problem is a result of populating your array inside a loop. In fact, running that other query inside a loop is very inefficient. Why don't you just have one query?

A little known feature of cfquery is that array functions work on query columns. In other words, you can do this.

select numeric_field

etc

</cfquery>

<cfoutput>#arraysum(q1["numeric_field"])#</cfoutput>

K

kt03Author

Inspiring

i don't know the way to combine all into one query.

sorry, i was not understand what you are saying about

select numeric_field

etc

</cfquery>

i did try to output #arraySum(countCommodity['totalRecs'])# but total stills 10 not 15.

D

Dan_Bracuk

Inspiring

If you don't know how to select from more than one table, I've heard good things about the book, Teach Yourself SQL in 10 Minutes.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded