Skip to main content
S
seasonedweb
Inspiring
October 7, 2020
Answered

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

  • October 7, 2020
  • 2 replies
  • 454 views

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.

This topic has been closed for replies.
Correct answer Charlie Arehart

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.

2 replies

Charlie Arehart
Community Expert
Charlie ArehartCommunity ExpertCorrect answer
Community Expert
October 7, 2020

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.

/Charlie (troubleshooter, carehart. org)
S
seasonedwebAuthor
Inspiring
October 12, 2020

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

 

Norman B.

Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
October 12, 2020

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

/Charlie (troubleshooter, carehart. org)
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
October 7, 2020

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices