CODEWITHSUNDEEP
sqlcoldfusion
ScrumMaster Guy
ScrumMaster Guy

Reputation: 267

ArraySum only a certain rows in the cfoutput of a query

I am trying to add only certain rows to this output. Currently this output adds all rows, which is used for a Total row at the very end of 

<cfoutput query="qrySummary">

#numberFormat(ArraySum(ListToArray(ValueList(qrySummary.secThreeCount))), ",")#

This obviously totals all of SecThreeCount values, but what if I want to exclude the last 2 rows from that list??

Is that possible in coldfusion?

(If this makes a difference)>> So there are 13 rows returning, I want the first 11 rows and exclude the last 2.

I know that I can limit the SQL return of that query to exclude those 2 rows, but I wanted less code to write and keep things neat. And also learn if it is possible:)

Thanks in advance.

Upvotes: 3

Views: 133

Answers (3)

duncan
duncan

Reputation: 31912

Another approach. You don't want to add up the values for the last 2 elements, so remove them from your array:

<cfset values = ListToArray(ValueList(qrySummary.secThreeCount))>

<!--- delete the last element --->
<cfset arrayDeleteAt(values, arrayLen(values))>

<!--- delete the last element again --->
<cfset arrayDeleteAt(values, arrayLen(values))>

#numberFormat(ArraySum(values), ",")#

Alternatively, given that you're looping over the query anyway, you could simply add the totals up as you go (with a tiny bit of logic to not bother if you're on the last or penultimate row)

Upvotes: 1

Adam Cameron
Adam Cameron

Reputation: 29870

Well I think if you don't need those two rows, you should not be returning them in the first place. That would be the best answer. From your statement "but I wanted less code to write" you're optimising in the wrong place: don't optimise for yourself, optimise for the solution.

Leigh's come in underneath me as I've been testing the code for this, but here's a proof of concept using subList():

numbers = queryNew("");
queryAddColumn(numbers, "id", "integer", [1,2,3,4,5,6]);
queryAddColumn(numbers, "maori", "varchar", ["tahi", "rua", "toru", "wha", "rima", "ono"]);

maori = listToArray(valueList(numbers.maori));
subset = maori.subList(2,5);

writeDump([numbers, subset]);

This returns an array with elements ["toru","wha","rima"].

Upvotes: 3

Leigh
Leigh

Reputation: 28873

If you are running CF10, one option is using ArraySlice. Grab only the first eleven elements, then apply arraySum.

  <cfset allValues = ListToArray(ValueList(qrySummary.secThreeCount))>
  <cfset subTotal = arraySum( arraySlice(allValues, 1, 11))>

For earlier versions, there is the undocumented subList(...) approach. It takes advantage of the fact that CF arrays are java.util.List objects under the hood, and uses List.subList(..) to grab a subset of the array.

Upvotes: 1

Related Questions