Summing Dollar Amounts from Query- Evaluate? Loop? CFSET? Let SQL DO IT?

[seasonedweb]

Greetings.

I posted this question about 5 years ago. Adding up payments by client. I still have not used this function - long story.

So the query comes  from 2 tables- one fetches the orders and the other clients to display  the client billing hduring a given period.

SELECT order_ID, order_number, order_client_ID, order_client_fee, order_status_ID, order_report_sent_date, order_property_street, order_property_city, client_ID, client_company, client_status

FROM main_orders, lookup_clients

WHERE order_client_ID = client_ID

AND order_client_ID = #FORM.client_ID#

AND order_status_ID = 9

AND client_status = 1

AND (order_report_sent_date BETWEEN #Session.StartDate# AND #Session.EndDate#)

ORDER BY order_report_sent_date ASC

Each payment diplays as #DollarFormat(order_client_fee)#

I simply need a sum column. Any help would be apreciated. The simpler the better! Thanks in advance.

[BKBK]

A sum column to display/calculate the sum of what? 

[Charlie Arehart · Accepted solution]

There are of course many ways to do it, each with pros and cons, but in terms of "the simpler the better" and given that you already have the code in CFML, you can do it in one line of code, starting with using CF's valuelist, a nifty and perhap under-used function that returns a list of all the values in a given col (in a given query). With that, you're halfway home.

As there's no function in CFML to add up such values in a list, you could instead convert that to an array, and then use CF's arraysum. Then you could format it as dollars, like you show using. 

So assuming that select was in a cfquery with a name of "myquery", the one line of code is this:

#DollarFormat(ArraySum(listtoarray(ValueList(myQuery.order_client_fee))))#

Again, lots of other ways to do it (doing a "group by" in sql, using "member functions" in CFML which would appeal more to some tastes, etc.), but let us know if this suffices.

[seasonedweb]

Charlie: Very much appreciate your solution. Works great! Thanks.

Norman B.

[Charlie Arehart]

Great to hear, Norman. And glad to have helped.