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

Community Beginner ,
Oct 07, 2020 Oct 07, 2020

Copy link to clipboard

Copied

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.

TOPICS
Advanced techniques

Views

117

Likes

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

correct answers 1 Correct Answer

Adobe Community Professional , Oct 07, 2020 Oct 07, 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 a...

Likes

Translate

Translate
Adobe Community Professional ,
Oct 07, 2020 Oct 07, 2020

Copy link to clipboard

Copied

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

Likes

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
Adobe Community Professional ,
Oct 07, 2020 Oct 07, 2020

Copy link to clipboard

Copied

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 (server troubleshooter, carehart.org)

Likes

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 Beginner ,
Oct 12, 2020 Oct 12, 2020

Copy link to clipboard

Copied

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

 

Norman B.

Likes

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
Adobe Community Professional ,
Oct 12, 2020 Oct 12, 2020

Copy link to clipboard

Copied

LATEST

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


/Charlie (server troubleshooter, carehart.org)

Likes

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